Copying and pasting multiple columns into another worksheet using vba

Question

I'm working on a code that will copy several columns of data that are not in order. I couldn't figure out how to do it in one step so i was trying to get it in two. After the first set of columns posts I'm having trouble getting it to go back to the sheet I was on to copy the next set of columns. This is what my code looks like so far.

Survey_Macro1 Macro

range("A:D").Select
range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add.Name = "Data"
ActiveSheet.Paste
ThisWorkbook.Save
ThisWorkbook.Sheets("Table").Activate
Application.CutCopyMode = False
range("AK:AL").Select
range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Worksheets("Data").range(E1).Select
ActiveSheet.Paste

score 4 · Accepted Answer · edited May 23 '17 at 11:46

See How to avoid using Select in Excel VBA macros.

Sub yg23iwyg()
    Dim wst As Worksheet

    Set wst = Worksheets.Add
    wst.Name = "Data"
    With Worksheets("Table")
        .Range(.Cells(1, "A"), .Cells(.Rows.Count, "D").End(xlUp)).Copy _
            Destination:=wst.Cells(1, "A")
        .Range(.Cells(1, "AK"), .Cells(.Rows.Count, "AL").End(xlUp)).Copy _
            Destination:=wst.Cells(1, "E")

        'alternate with Union'ed range - becomes a Copy, Paste Special, Values and Formats because of the union
        .Range("A:D, AK:AL").Copy _
            Destination:=wst.Cells(1, "A")
    End With

End Sub

Thank you so much Jeeped, this was a huge help! – Cocoberry2526 Mar 15 '17 at 19:01 — Cocoberry2526, Mar 15 '17 at 19:01

Copying and pasting multiple columns into another worksheet using vba

1 Answers1