Copy entire columns to separate worksheets

[exceldb]

Hi Everyone,

I am a newbie to this forum, though not necessarily new to the rules.
I have a very simple task to complete, yet no matter how many youtube videos I watched and how many different forums and pages I have read, none of them are this simple.

I did see a function called entirecolumncopy in one vba script, so I am hoping it can be done.

I basically have a master spreadsheet and I want to be able to copy the first column and subsequent columns to separate worksheets.
Now, I do not want to create the headers first, just copy something based on a location or department, I want a full copy of columns.

The end result being worksheets that contain the following

Sheet1 = Column A + Column B
Sheet2 = Column A + Column C
Sheet3 = Column A + Column D
.....
Column A of course being the unique identifier, in another words I want the sheets to act like a relational database where the primary key is always following the data.

Also, after reading a few threads, I want to add different items I see people ask for when trying to help.

1. The original name of the master spreadsheet should not matter
2. The vba should create as many sheets as there are many columns - so if 25 columns, then 25 sheets and if tomorrow I add a 26th, it will do that.
3. The names of the sheets should simply be the header for the second column ( In other words, if the header for column B in Sheet2 was location, the sheet should be "location"
4. The master spreadsheet should not be touched as it could be used for vlookups and other functions.

Last but not least, does anyone recommend a really good book for excel and vba - from beginner to advanced. There are a ton and I figured I would ask here.

Thank you in advance for all your help!
Tony

 

[exceldb]

I just ran it one more time and noticed it only copied the first three columns and stopped. So, B, C, D columns were copied and then it stopped.

 

[Fluff]

In that case, can you please supply the code you're using.

 

[exceldb]

I just copied and pasted what you provided - was I not supposed to do that?

Sub exceldb()
Dim Ary As Variant, ColAry As Variant
Dim i As Long

ColAry = Array(Array(1, 3, 5, 7), Array(1, 2, 4, 5, 6), Array(1, 8, 9, 10))
Ary = Range("A1").CurrentRegion.Value2
For i = 2 To UBound(ColAry(UBound(ColAry))) + 1
Sheets.Add(, Sheets(Sheets.Count)).Name = Ary(1, i)
Range("A1").Resize(UBound(Ary), UBound(ColAry(i - 2)) + 1).Value = Application.Index(Ary, Evaluate("row(1:" & UBound(Ary) & ")"), Array(ColAry(i - 2)))
Next i
End Sub

 

[Fluff]

I just copied and pasted what you provided - was I not supposed to do that?

That's exactly what you should have done.
The reason I asked is that code should not be copying columns B:D.
The first sheet should have columns A, C, E & G
The 2nd cols A,B,D,E & F
The 3rd cols A,H,I & J

 

[exceldb]

I thought that is what you were doing when building the array in the order 1,3,5,7. But it just copies the first three with no data and stops.
Not sure if it matters but I am using office 365.

 

[Fluff]

When you say

it just copies the first three with no data and stops.

Do you mean that you get three sheets with no data in any of them?
Also does your data start in A1 & do you have any blank rows or columns within the data?

 

[exceldb]

Correct. Three sheets are created. Sheet names correspond to Column headers of B,C,D. No data in any of the sheets. My data does start in A1 and there are no blank values in between but I could have empty values in the other columns or rows.

 

[Fluff]

If you run this, what does the msgbox say?

Sub exceldb()
   Dim Ary As Variant, ColAry As Variant
   Dim i As Long
   
   ColAry = Array(Array(1, 3, 5, 7), Array(1, 2, 4, 5, 6), Array(1, 8, 9, 10))
   Ary = Range("A1").CurrentRegion.Value2
   MsgBox UBound(Ary) & vbLf & UBound(Ary, 2)
'   For i = 2 To UBound(ColAry(UBound(ColAry))) + 1
'      Sheets.Add(, Sheets(Sheets.Count)).Name = Ary(1, i)
'      Range("A1").Resize(UBound(Ary), UBound(ColAry(i - 2)) + 1).Value = Application.Index(Ary, Evaluate("row(1:" & UBound(Ary) & ")"), Array(ColAry(i - 2)))
'   Next i
End Sub

 

[exceldb]

756
52

Which is right on the money as I have 756 rows with data and 52 columns.

 

[Fluff]

Ok, that rules out a couple of possible problems.
If you step through the code using F8, after it has added a new sheet, is that sheet the active sheet?