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]
>
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