I used this:
Sub CopySheets()
Dim SourceWB As Workbook
Dim DestWB As Workbook
Dim sht
ThisWorkbook.Activate
Application.ScreenUpdating = False
Set SourceWB = ThisWorkbook
Workbooks("DestBook.xlsx").Activate
Set DestWB = ActiveWorkbook
SourceWB.Activate
Err.Clear
For sht = 5 To SourceWB.Sheets.Count
Application.StatusBar = "Sheets Remaining: " & SourceWB.Sheets.Count -
sht
SourceWB.Sheets(sht).Copy After:=DestWB.Sheets(DestWB.Sheets.Count)
Next sht
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox "Finished"
End Sub
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: sspatriots <sspatriots@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Wed, September 28, 2011 8:38:48 AM
Subject: [ExcelVBA] Re: Select all Worksheets after the first 4 to copy
I'm hoping someone can give me a hand with this one. I think I've managed to
work around the other two topics I've posted yesterday.
Thanks,
Steve
--- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@...> wrote:
>
> I have the following in my code to select some worksheets that appear after the
>first 4 worksheets.
>
> Sourcewb.Sheets(Array("Brake","Deburr")).Copy
> Set Destwb = ActiveWorkbook
>
> However, I want to be able to select all the worksheets after the first 4
>worksheets. The names of these worksheets will always vary. I'm not sure of how
>to use the "Sourcewb.Sheets... bit and come up with a way to copy all those
>remaining sheets. They ultimately get pasted into the "Destwb".
>
>
> Thanks,
>
> Steve
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar