VBA Macro based on a variable list

[Brass02]

Hello,

I have a workbook with 220 tabs. My first tab "3print" has 18 lists, each with a different combination of the 220 tab names. Below I am showing one of those lists.

I would like to have a macro that uses the list to copy the tabs into a new workbook. The names of the tabs never change, but the list does on occasion.

In simple words, the macro should execute as "Find tab "3print", locate list in range G60:G89, find the tabs with those names in the list, copy into new workbook".

Below is a list for Central Florida accounts that I work with. Each name below is the name of a tab. In cell G59 of the 3print tab I have the name "Central Florida" as the title for the list. If possible, could the new workbook have this title as the workbook name as well?

Your help will be forever appreciated! Thank you!

BJs Wholesale Club6Wk Calendar
BJs Wholesale ClubMAP
Bravo 6Wk Calendar
Bravo MAP
Costco Wholesale 6Wk Calendar
Costco Wholesale MAP
Hitchcock 6Wk Calendar
Hitchcock MAP
IGA Florida 6Wk Calendar
IGA Florida MAP
JH Harveys6Wk Calendar
JH HarveysMAP
K-Mart 6Wk Calendar
K-Mart MAP
Publix Florida6Wk Calendar
Publix FloridaMAP
Sam's Club 6Wk Calendar
Sam's Club MAP
Sedanos 6Wk Calendar
Sedanos MAP
Target 6Wk Calendar
Target MAP
Wal-Mart6Wk Calendar
Wal-MartMAP
Winn-Dixie 6Wk Calendar
Winn-Dixie MAP
Winn-Dixie SFL6Wk Calendar
Winn-Dixie SFLMAP
SavALot6Wk Calendar
SavALotMAP

 

[Debraj]

Hi Brass02,

Welcome tot he forum..

Just for take a start, try this..

Sub CopySheets()
With Sheets("3print").Range("G59:G89")
For i = 2 To .Cells.Count
    Sheets(Split(.Cells(i), " ")).Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & .Cells(1) & " - " & .Cells(i) & ".xlsx", 51
    ActiveWorkbook.Close False
Next i
End With
End Sub

Please let us know the feedback.

 

[Brass02]

Hello Debraj,

Code looks good, though when I run it I get an error message and when I debug it highlights in yellow "Sheets(Split(.Cells(i), " ")).Copy"

Thanks,

Brass02

 

[Debraj]

Are you trying it in my provided sheet, or in your actual sheet..

Please check..
* If tab name consist of space or not.
* Assuming, all 220 sheets are already with you.. and there was no spelling mistake in between G59:G89,

My provided sheet is just for testing purpose.

In case if you are facing any issue, you can upload a sample file (only tab name required, no need any data in other sheets except 3Print)

 

[Brass02]

Debraj,

Attached is an excel sheet of my 3Print tab with the exact cell locations of my lists and with the exact names of my tabs.

 

[Brass02]

I was trying it in my actual workbook.

 

[Brass02]

When I try it in your sheet I get the same error message and the same line of code highlighted in yellow.

 

[SirJB7]

Hi, Brass02!
Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from th right excluding the last and separated one.
Regards!

 

[NARAYANK991]

Hi ,

See the file now.

Narayan

 

[Debraj]

@NARAYANK991
Now I re-read your post and checked your code.. now its making sense..
Did some random test, and looks perfect... as the max length of sheet name is "Super Indy Grocery 6Wk Calendar" perfect 31 Character.

@Brass02 ,

* Please check the updated one, to cover all "Large Format" (G59:N59)
* Assuming "Sheet name" are must present in the working Workbook.

Sub CopySheets()
With Sheets("3print").Range("G59:N59")
    For i = 1 To .Columns.Count
        Sheets(Application.Transpose(.Cells(2, i).Resize(.Cells(i, 1).End(xlDown).Row - 59))).Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & .Cells(1, i) & ".xlsx", 51
        ActiveWorkbook.Close False
    Next i
End With
End Sub

If its working, you caa do the same for "Small Format also"

Please let us know the feedback, as its untested..