Depends of what columns are selected create a new sheet

Question

I want to create a new table from a main tabla based o columns I manage to create a code but the columns have to be always the same, I would like to create a template code that besed on the columns I select on the main tab creates a new table on a new sheet, and could be different from one time to another so I can create a quick access select my columns click the button and done new table on the new sheet

Sub CopyColumns()

    Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    Columns("A:A").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("B:B").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("C:C").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("D:D").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Sheets("Sheet1").Columns("E:E").Copy Destination:=Sheets("Sheet2").Range("E1")

    Sheets("Sheet1").Select

End Sub

Sub CopyColumns()

    Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    Columns("A:A").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("B:B").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("C:C").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Columns("D:D").Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll
    Sheets("Sheet1").Columns("E:E").Copy Destination:=Sheets("Sheet2").Range("E1")

    Sheets("Sheet1").Select

End Sub

Select columns (not the same every time ) a create a new table everytime I run the code with different selection

You should read this: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — erazorv4, Jun 07 '19 at 12:00

score 3 · Answer 1 · answered Jun 07 '19 at 12:19

3

Quick example if you must use your selection...

Sub CopyColumns()

Dim mySel As Range
With ActiveWorkbook
    Set mySel = Selection.EntireColumn
    .Sheets.Add After:=Worksheets(Worksheets.Count)
    mySel.Copy Destination:=.Sheets(.Sheets.Count).Range("A1")
End With

End Sub

answered Jun 07 '19 at 12:19

JvdV

70,606
8
39
70

@anamariasalazarmontes, if this has answered your question, consider marking your question as answered. Have a look [here](https://stackoverflow.com/help/someone-answers) – JvdV Jun 07 '19 at 13:00

Depends of what columns are selected create a new sheet

1 Answers1