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]

Hi Fluff,
I am running the code and for the first time I have real data and something interesting is happening.
When I changed the array membership to different columns, I get different results.
In the current example, It only creates 5 different worksheets instead of the 6 that I include in the array.
If I keep the original code it creates more than 6 but then it errors out.
I do not know enough on the construction of the vba code to try to figure out where and how these sheets are getting created.

Does this outcome make any sense?

Thanks!

 

[Fluff]

Can you post the code you are currently using?

 

[exceldb]

Sub exceldb2()
   Dim Ary As Variant, ColAry As Variant
   Dim i As Long
   
   ColAry = Array(Array(1, 21), Array(1, 22), Array(1, 27), Array(1, 29), Array(1, 30), Array(1, 31))
   Ary = Range("A1").CurrentRegion.Value2
   For i = 2 To UBound(ColAry) + 1
      Sheets.Add(, Sheets(Sheets.Count)).Name = Ary(1, ColAry(i - 2)(1))
      Sheets(Ary(1, ColAry(i - 2)(1))).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

I just changed the ColAry value statement

 

[Fluff]

As you're only copying 2 columns try

Sub exceldb2()
   Dim Ary As Variant, ColAry As Variant
   Dim i As Long
   
   ColAry = Array(1, 21, 22, 27, 29, 30, 31, 34)
   Ary = Range("A1").CurrentRegion.Value2
   If UBound(Ary, 2) < ColAry(UBound(ColAry)) Then
      MsgBox "Not enough columns"
      Exit Sub
   End If
   For i = 1 To UBound(ColAry)
      Sheets.Add(, Sheets(Sheets.Count)).Name = Ary(1, ColAry(i))
      Sheets(Ary(1, ColAry(i))).Range("A1").Resize(UBound(Ary), 2).Value = Application.Index(Ary, Evaluate("row(1:" & UBound(Ary) & ")"), Array(ColAry(0), ColAry(i)))
   Next i
End Sub

 

[exceldb]

I am trying to copy 6 columns, that is what I thought 22,25,26, etc were.
Am I wrong?

Thanks,
Carlos

 

[Fluff]

Your code is looking to create 6 sheets with 2 columns on each

 

[exceldb]

Your code is looking to create 6 sheets with 2 columns on each

That is correct. Will run new code now and report.
Thanks.