Cell data of One Sheet Select Automatically copies in a cell of Other Sheet.

**KjBox** · Jun 30th 2021

Sorry, my misunderstanding.

Change the code to this

Code

Sub Button1_Click()
    Dim x(), r As Range, c As Range
    Set r = Range(Selection.Address)
    ReDim x(1 To 1)
    
    For Each c In r
        If c.Column = 9 And c.Row > 8 And c.Row < 1201 _
        And c <> vbNullString Then
            i = i + 1: ReDim Preserve x(1 To i)
            x(i) = c
        End If
    Next
    With Sheets("Number").Cells(9, 4)
        .Resize(100).ClearContents
        .Resize(i) = Application.Transpose(x)
    End With
    MsgBox "Selection copied  to Number"
    Application.Goto Cells(8, 9)
    
End Sub

Display More

mamun.ges · Jun 30th 2021

You are Good. I appreciate your work and support .

Wish you a healthy and happy life by .

Thanks and best regards.

**KjBox** · Jun 30th 2021

You're welcome

mamun.ges · Jun 30th 2021

Dear Kjbox,

What is this line means

Code

.Resize(100).ClearContents

This line gives me an error in my workbook but not in your sample workbook.

But if I remove the above code It copies data but after another selection, previous data remain.

So this line of code clears the data to the destination sheet (Number).

my destination sheet (Number) has 6 cells from D9:D14.

Will you explain what should I have to do about this error.

Thank you.

**KjBox** · Jun 30th 2021

That clears any data from a previous copy paste. It clears 100 rows (D9 to D108). I had no way of knowing how many entries there might be so I reckoned 100 would be more that enough.

So change that to

Code

 .Resize(6).ClearContents

mamun.ges · Jun 30th 2021

Dear Kjbox, it gives me an error in the same line.

**KjBox** · Jun 30th 2021

What error do you get?

mamun.ges · Jun 30th 2021

HI, it Says run time error "1004"

Can not change part of a merged cell

**KjBox** · Jun 30th 2021

That is the reason then. Merged cells are a headache in waiting! They should be avoided whenever possible, and never be in data tables.

If you have to have merged cells can you attach an example workbook with identical merged cells

**KjBox** · Jun 30th 2021

Also what is being copied? Numbers, short text or long text?

mamun.ges · Aug 28th 2021

Dear KjBox,

Should I ask same type of query in this thread?

If ok Then read it Otherwise ignore it.

"Search and copy-paste"

Two worksheets, Bill and Support

Four cells in worksheet "Support" (Cells are I20, M20, R20, T20)

Four-column in worksheet "Bill" ( I, W, X Y) which start from 9 -1220

If the command button is clicked.

I20 value of "support" search in between I9:I1220 of Bill Worksheet.

If match found then M20, R20, and T20 Value paste in the corresponding row of W, X, Y.

and give a massage "value updated"

Hope you help me.

Thanks in advance.

mamun.ges · Aug 28th 2021

Hi, kjBox

I want to let you know that I posted about this issue on the forum link below.

https://www.excelforum.com/exc…py-paste.html#post5561557

Thanks for your support.

Cell data of One Sheet Select Automatically copies in a cell of Other Sheet.

Participate now!

Share

Users Viewing This Thread