VBA Help on Splitting Worksheet

0 votes

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

Jan 19, 2023 in Others by Kithuzzz
• 38,000 points
341 views

1 answer to this question.

0 votes

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.

answered Jan 19, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Excel VBA Worksheet Range storage location best practices

Solution Changing Worksheets' Code Name Why dim srcSheet as myWorksheet when ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,600 points
450 views
0 votes
1 answer

Activate last excel worksheet on worksheetlist with closedxml in c#

This will answer your question :  Changing ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,600 points
1,805 views
0 votes
1 answer

Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
628 views
0 votes
1 answer

How to add text on new page in word doc via excel vba

First start with: objDoc.Range.Insertafter vbCr & Chr(12) & ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,913 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,246 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,692 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
956 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,084 views
0 votes
1 answer

VBA Help to find a column based on header value and cupy it to an other worksheet

You can break out the "copy column ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,600 points
2,412 views
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
2,968 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP