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?
 
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
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Thanks
0
 
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
 
JagwarmanAuthor Commented:
Thanks for your patience Rory
0
 
Rory ArchibaldCommented:
No problem - glad to help. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.