Copy an array of worksheets to another workbook

[PWD]

I need to copy some worksheets from a workbook. I found this code to help me copy multiple worksheets from one workbook to another:-

      With Workbooks("Workbook1.xls")
        .Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy _
            Before:=Workbooks("Workbook3.xls").Sheets(1)
    End With

as I couldn't get a loop to work. (Basically I couldn't get the focus back to the original workbook after it had completed the 1st pass & copied (from an array "MySheets") the 1st worksheet to the current workbook using this code)

For CA = 1 To x - 1 'Copy all the named worksheets from source file to present file
Sheets(MySheets(CA)).Copy After:=Workbooks("LBL spec w3wk5 Test_Version.xlsx").Sheets(WSS + CA - 1) 'WSS = Worksheets.Count
Next CA

I tried things along the lines of:-

Windows(sourcefile).Select

So what I'd like to do now is (somehow) use the array of sheet names that I already have, to determine the

.Copy

array as I want it to be more flexible than:-

.Sheets(Array("q1a", "q3_1_corporate_other", "q3_2_other_spec")).Copy

Can it be done? Or what would be the best way to achieve this?

Many thanks,

Des

 

[SkipVought]

hi,

I couldn't get the focus back to the original workbook after it had completed the 1st pass & copied

try this...

dim ws as worksheet

    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Sheet1", "Sheet2"  'list the sheets NOT to copy
            Case Else
            'copy here
                With Workbooks("LBL spec w3wk5 Test_Version.xlsx")
                    ws.Copy After:=.Sheets(.Sheets.Count) 'copys after last sheet
                End With
        End Select
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

I like your thinking Skip, but there are 10 I want out of 25 or so

Des.

 

[Gammachaser]

Looks like the sheets have no name (sheet1, sheet2, etc.) Can you rename the sheets to start with some character like '+' (+Sheet1, +Sheet2 would even work) to distinguish which ones you want to copy? Then you could use Skip's code with a slight modification.

Select Case ws.Name Like "+*"

Just a thought.

 

[SkipVought]

HOW you define the sheets to copy is another issue.

The primary issue is how to "copy multiple worksheets from one workbook to another."

So this code answers THAT question!


Now for the list of diverse sheet names:

Make a list on a sheet.

Rather than looping thru all the sheets in ThisWorkbook, loop thru the list. The principle is the same.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

I agree Skip (and I agree that I already had a working solution) but I wanted to be able to (array) copy from my array "MySheets" as I couldn't get the loop to re-focus on source workbook.

Des.

 

[SkipVought]

The source workbook is ThisWorkbook.

With my code, there is no problem like that.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Good morning Skip. Hope you had a good weekend. I probably don't explain things properly - causing a bit of confusion. Apologies!

I'm starting with "ThisWorkbook" & opening another (source) workbook. I have a Range on ThisWorkBook called "SheetNames".

I create an array of these names:-

data = Range("SheetNames").Count
ReDim MySheets(1 To data)
x = 1

For Each c In Range("SheetNames")
MySheets(x) = c
x = x + 1
Next c

I then get the source file open.

ChDrive "G" 'Change the Drive letter to "G"

ChDir "\DP\sampling\Ben\LBL Verbatims\"

     sourcefile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
        If sourcefile = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        Exit Sub
        Else
        Workbooks.Open Filename:=sourcefile
        End If

When I try this loop:-

        For CA = 1 To x - 1 'Copy all the named worksheets from source file to present file
Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(WSS + CA - 1)
        Next CA

it's OK for the 1st Copy & Paste but I need to get the focus back to the source file to do any more. Grrrr!

I've tried

sourcefile.Activate

in the loop but that does.t work. I tried

sourcefile.Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(WSS + CA - 1)

in the loop instead but this didn't work at all.

Des.

 

[SkipVought]

COMPLETE Object references are EXTREMELY important when you have this instance of multiple workbooks or multiple worksheets or ANY multiple object occurrence!

[b]
ThisWorkbook.[/b]Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(WSS + CA - 1)

Which is why this also works, because the references are explicit for each object...

                With Workbooks("LBL spec w3wk5 Test_Version.xlsx")
                    ws.Copy After:=.Sheets(.Sheets.Count) 'copys after last sheet
                End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Double rats.

I get an error:

"Subscript out of range"

when I use:

ThisWorkbook.Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(WSS + CA - 1)

Is that because they are coming from 'sourcefile' and going to "ThisFile"/ThisWorkbook?

Des.

 

[PWD]

MsgBox ActiveWorkbook.Name

shows that it is the target workbook that has the focus. I need to get back to the source file to do the next copy. (Or find a way of creating a copy array from my "MySheets" array!)

Des.

 

[PWD]

I just tried:

Windows(sourcefile).Activate

BUT. This is the whole file path as well as the file name. Would it work if I just used the file name?

I'll have to break it down and give it a go.

Des.

 

[SkipVought]

You do NOT need to ACTIVATE anything if your objects are properly referenced!

"Subscript out of range" means an undefined reference.

Hit your DEBUG button and observe the values of your variables and state of your objects, using faq707-4594


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

OK. So this appears to work.

After:-

Workbooks.Open Filename:=sourcefile

I just grab the file name:-

        SF = ActiveWorkbook.Name
        
        For CA = 1 To x - 1 'Copy all the named worksheets from source file to present file
            Workbooks(SF).Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(WSS + CA - 1)
        Next CA

Many thanks,

Des.

 

[PWD]

This works as well:-

        For CA = 1 To x - 1 'Copy all the named worksheets from source file to present file
         Workbooks(SF).Sheets(MySheets(CA)).Copy After:=Workbooks(ThisFile).Sheets(Workbooks(ThisFile).Sheets.Count)
        Next CA

Des

 

[PWD]

Final answer,

Workbooks(SF).Sheets(MySheets(CA)).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Des.