Copy selected columns and paste in a different worksheet

[sahaider]

Hello all,
Can anyone help me with this?
I would like to copy selected columns from a worksheet and paste it in cell A1 of another sheet.
the columns to be selected are columns B to D and column H and paste the copied contents in cell A1 of another sheet called TEST1.

Here is the code I have so far:

Range("B2:D2","H2").Select
Range(Selection, Selection.End(xlDown)).Select



Application.CutCopyMode = False
Selection.Copy

Worksheets("TEST1").Select
Range("A1").Select
ActiveSheet.Paste


The above code is not copying all the contents to the destination worksheet TEST1

Thanks

 

[Saurabhj]

Hi,

Quick solution -

     ActiveSheet.Range("B:D").Copy Destination:=Worksheets("TEST1").Range("A1")
    ActiveSheet.Range("H:H").Copy Destination:=Worksheets("TEST1").Range("D1")

Thanks,
Saurabh

 

[vcoolio]

Hello Sahaider,

Another option:-

Sub Test()
        
        Dim ws1 As Worksheet: Set ws1 = Sheet1
        Dim wsT As Worksheet: Set wsT = Sheets("TEST1")
        
Application.ScreenUpdating = False

        wsT.UsedRange.Clear

        With ws1.[A1].CurrentRegion
               Union(.Columns("B:D"), .Columns("H")).Copy wsT.[A1]
        End With

Application.ScreenUpdating = True

End Sub

The code clears the "TEST1" sheet prior to each data transfer.
The code will also take the headings over to the "TEST1" sheet.
Please test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.