This isn't my first rodeo...
I've done this before and found the same thing you did.
You had to keep entering the total number of pages...
So I made the macro think for itself.
There's a couple of steps like activating ThisWorkbook,
setting SourceWB to thisworkbook, and others
that may not be necessary if it's already known by the time you reach this part
of the
macro, but for testing purposes, I needed to set them...
good luck.
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 12:28:16 PM
Subject: [ExcelVBA] Re: Select all Worksheets after the first 4 to copy
Thanks Paul. That looks like it will do just what I'm after. I had something
that was a lot more drawn out and it also required an exact number of worksheets
to be pre-determined.
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> 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@...>
> 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]
>
[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