Select a sheet base on text in cell

I am copying Sheet 1 from my existing workbook into a new workbook. I then need to select other sheets and copy those to the same workbook.

So I do the following:


Dim j As Workbook
            Set j = ActiveWorkbook

    Wb.Activate
   
    Range("A1:I37").Select
    Selection.Copy
    j.Activate
    Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I then need to copy other sheets from Wb to J based on the contents of cells in range A20-A29

So, if Cell A 20 = Booking 1 I need to copy the sheet from Wb to J and name the sheet Booking 1
if Cell A21 = Booking 2, copy the sheet Booking 2 to J and name it Booking 2
and so on until A29

Appreciate your help experts.
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
I think you mean:

Dim cell as Range
for each cell in sheets("some sheet").range("A20:A29")
wb.sheets(cell.value).copy after:=j.sheets(j.sheets.count)
next cell

Open in new window


Really you should specify the workbook for the A20:A29 range as well as the sheet name, but I don't know which that is! :)
0
JagwarmanAuthor Commented:
additional information:

Wb is the Active workbook from which the sheets will be copied.

The sheet name is Rec

j is the the workbook I will be copying the sheets to.

Hope this helps
0
JagwarmanAuthor Commented:
cells A20 A29 must contain "Booking 1 or 2 or 3 to 10

Thanks
0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Rory ArchibaldCommented:
On which sheet in which workbook is the A20:A29 range located?
0
JagwarmanAuthor Commented:
sorry to confuse you:

On which sheet in which workbook is the A20:A29 range located?  In sheet REC in the Wb [which is the Active workbook]
0
Rory ArchibaldCommented:
OK, so the code becomes:

Dim cell as Range
for each cell in wb.sheets("REC").range("A20:A29")
wb.sheets(cell.value).copy after:=j.sheets(j.sheets.count)
next cell

Open in new window

0
JagwarmanAuthor Commented:
Hi Rory, I am getting subscript out of range
0
Rory ArchibaldCommented:
OK - do the cells not all contain valid sheet names?
0
JagwarmanAuthor Commented:
I have attached a file [taken out data] to show what the main file looks like hope this helps
Booking.xlsx
0
Rory ArchibaldCommented:
If they are either blank or valid names, you can use:

Dim cell as Range
for each cell in wb.sheets("REC").range("A20:A29")
if len(cell.value) <> 0 then wb.sheets(cell.value).copy after:=j.sheets(j.sheets.count)
next cell

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Thanks for your patience Rory
0
Rory ArchibaldCommented:
No problem - glad to help. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.