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

mamun.ges · Jun 26th 2021

Hi,

I want to simplify something in my excel sheet. But don't know how to do it. The idea describes below.

I have a large datasheet name bill. In this sheet Column, I9:I1200 has data but not in every cell.

Now when I select one of the cell in between of I9:I1200 which cell has data,

The vba code automatically copies the selected cell data to another two sheets name 'Bill Info' to D12 and sheet 'Other' to G13 for calculating those sheets.

If multiple cells or the cell which have no data select in the range of I9:I1200, It returns nothing.

Is it possible? If possible please let me know What will be the code.

Thanks.

**KjBox** · Jun 26th 2021

Try this, put it in the Worksheet Object Module for the data sheet "Bill"

Code

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 9 Then
        If Target = vbNullString Then
            Exit Sub
        Else
            Sheets("Bill Info").[d12] = Target
            Sheets("Other").[g13] = Target
        End If
    End If
    
End Sub

Display More

mamun.ges · Jun 26th 2021

You are so good, I am loving it.

Wish I could do help the way you do.

The above code is like magic,

now,

What if I select data I9:I1200 and selected row Y cell data also copied and paste to Sheets"Other" in cell G14 too.

What should the code be?

Thanks.

**KjBox** · Jun 26th 2021

Try

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 9 Then
        If Target = vbNullString Then
            Exit Sub
        Else
            Sheets("Bill Info").[d12] = Target
            With Sheets("Other")
                .[g13] = Target
                .[g14] = Cells(Target.Row, 25)
            End With
        End If
    End If
    
End Sub

Display More

mamun.ges · Jun 26th 2021

It's astonishingly Perfect. Hats off to you.

Thank you again.

mamun.ges · Jun 29th 2021

Hi Kjbox, Hope you are fine.

The above code works fine in my workbook. But in some cases, I wish multiple cells to select and which will be copied and displayed to other sheets column-wise.

The Source Sheet is the same "Bill" but the destination sheet change is "Number"

Suppose I select 5 different cells in between I9:I1200 (i.e. I9, I13, I15, I122, I550) they will be copied and show in the "Number" Sheet as column-wise D9:D13

if I select 3 different cells in between I9:I1200 (i.e. I9, I13, I15) they will be copied and show in the "Number" Sheet as column-wise D9:D11

if I select 7 different cells in between I9:I1200 (i.e. I9, I13, I15, I122, I550, I1120, I1200) they will be copied and show in the "Number" Sheet as column-wise D9:D15

Can it be done in the existing code you created above?

Thanks in advance

**KjBox** · Jun 29th 2021

It is not possible to adapt the code for use with multiple cell selection, unless the cells are continuous (e.g. I9, I10, I11)

The only way would be to have the code assigned to a button, which would need to be clicked after the selection of either a single cell or multiple cells had been made

mamun.ges · Jun 29th 2021

Thank you for your response and I glad to hear from you.

If it has to use a button for this then what will be the code.

Please.

**KjBox** · Jun 29th 2021

Just to be clear, if a single cell is selected then copy is to sheet "Bill Info" and "Other", if multiple cells selected then copy is to "Number"?

mamun.ges · Jun 30th 2021

Yes Kjbox,

You are absolutely right. when multiple cell selected copy to "number". Other condition is unchanged for "Bill info and other".

Sometimes the source file "bill" is auto fliterd or not.

Thanks and appreciate your help.

**KjBox** · Jun 30th 2021

Try the attached test file.

Code assigned to the button

Code

Sub Button1_Click()
    Dim x(), r As Range, c As Range
    
    Set r = Range(Selection.Address)
    With r
        If .Count = 1 Then
            If r.Column = 9 And r.Row > 8 And r.Row < 1201 And r <> vbNullString Then
                Sheets("Bill Info").[d12] = r
                With Sheets("Other")
                    .[g13] = r
                    .[g14] = Cells(r.Row, 25)
                End With
                MsgBox "Selection copied to Bill Info and Other"
                Application.Goto Cells(8, 9)
            End If
        ElseIf .Count > 1 Then
            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
            Sheets("Number").Cells(9, 4).Resize(, i) = x
            MsgBox "Selection copied  to Number"
            Application.Goto Cells(8, 9)
        End If
    End With
    
End Sub

Display More

**KjBox** · Jun 30th 2021

Auto Filtering will have no effect, the code works on the cell(s) you select. Obviously you cannot select a filtered out cel!

mamun.ges · Jun 30th 2021

Thanks for your feedback Kjbox, and the sample fie you created.

In the sample file data copied row-wise but in the "number" sheet, the data should to copied column-wise start from D9:D11. It always starts at D9.

When I select 5 cell data of "BILL" & after clicking the button it copied to D9 to the next 4 cells below in "Number" Sheet

after that, If I select 3 data of "Bill" & after Clicking the button it copied to D9 to next 2 cells below in "Number" Sheet but there previous 2 data remain of the previous click. It should be refresh. only what data select remain there. if selection changes then the data will be change.

Thanks again.

**KjBox** · Jun 30th 2021

OK I understand, so the only data from D9 onwards on the Number sheet will contain only any previously pasted data?

mamun.ges · Jun 30th 2021

"Please read the above massage with the below"

Hi Kjbox, Please can the code only made for the "Number" worksheet.

If the button clicked it copied the selected data to the "number" worksheet, not "the other & Bill info" Sheet.

The button click option workable only for "Number".

Thanks

**KjBox** · Jun 30th 2021

Yes it can, but you cannot use the Worksheet_SelectionChange code for copying a single cell to Bill Info and Other as well as having a button to copy just multiple selections.

The reason being, as soon as you select the first cell of a multi-selection the the Worksheet Event code will fire.

**KjBox** · Jun 30th 2021

So a single cell selection copies to D9 in Number sheet, and multi-selection to D9 onwards?

mamun.ges · Jun 30th 2021

Yes, Boss. (Removing older selection and downward from D9 to....).

Thanks

**KjBox** · Jun 30th 2021

Well that simplifies the code!

Change the code assigned to the button to

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) = x
    End With
    MsgBox "Selection copied  to Number"
    Application.Goto Cells(8, 9)
    
End Sub

Display More

mamun.ges · Jun 30th 2021

Hi, KJbox

The data copied to the "Number" sheet row-wise (Like D9, E9, F9) but it should copy like D9, D10, D11.

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

Participate now!

Share