VBA Help on Splitting Worksheet

Question

I need to separate the data on a spreadsheet. For instance, I have columns A, B, C, and D. I need to divide the sheet into the following parts:

Columns A and B on the first sheet Columns A & C on the second sheet Columns A and D on page three.

The constant here must be column A, which applies to about 350 columns.

I have the below code:

Sub t()
Dim lc As Long, sh As Worksheet, newSh, ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set sh = ActiveSheet
With sh
    lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    For i = 1 To lc
        If Application.CountA(.Columns(i)) > 0 Then
        
            Set newSh = Sheets.Add
            ws1.Range("a:a").Copy Range("a:a")
            Intersect(.UsedRange, .Columns(i)).Copy newSh.Range("A1")
            newSh.Copy
        
            ActiveWorkbook.SaveAs newSh.Range("a1").Value & ".xlsx"
            ActiveWorkbook.Close
            Application.DisplayAlerts = False
            newSh.Delete
            Application.DisplayAlerts = True
        End If
    Next
End With
End Sub

But this only splits out the individual columns, I need to add column A each time

Answer 1

In this bit of code:

ws1.Range("a:a").Copy Range("a:a")
Intersect(.UsedRange, .Columns(i)).Copy newSh.Range("A1")

Your output to column A both times. So column A is copied each time but overwritten by whichever other column you copy. Fix this by outputting to "B1" on the second line.