Copying multiple worksheets into a new workbook
 

Hey guys,

I’m new to this VBA stuff and could use some help. I'm trying to cop
several worksheets into a new workbook. I'm using a loop to check
couple of Boolean fields to see if the sheet should be copied, if it'
true I would copy into a brand new workbook. However, I get stuck o
the second approach of the loop…

Here's my code...Any ideas what I'm doing wrong?
Any Assistance would greatly be appreciated!!

Sub Macro_test()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim IncludeSheet As String
Dim DataSheet As String
Dim Number As Integer

Set wbk1 = ActiveWorkbook
Set wbk2 = Workbooks.Add

wbk1.Activate
Number = 1

For CounterComp = 6 To 15

IncludeSheet = wbk1.Worksheets("Landscap
Labels").Cells(CounterComp, 31)
DataSheet = wbk1.Worksheets("Landscape Labels").Cells(CounterComp
7)

If IncludeSheet = True Then
Sheets(DataSheet).COPY After:=wbk2.Sheets(Number)
Number = Number + 1

Else
Number = Number + 1

End If

Next CounterComp

End Su

--
Message posted from http://www.ExcelForum.com

Copying multiple worksheets into a new workbook
 

You must declar
Dim IncludeSheet As Boolean (not string
.... now your test
if IncludeSheet = true then...
will wor

----- anhjan wrote: ----

Hey guys,

Im new to this VBA stuff and could use some help. I'm trying to cop
several worksheets into a new workbook. I'm using a loop to check
couple of Boolean fields to see if the sheet should be copied, if it'
true I would copy into a brand new workbook. However, I get stuck o
the second approach of the loop€

Here's my code...Any ideas what I'm doing wrong
Any Assistance would greatly be appreciated!

Sub Macro_test(

Dim wbk1 As Workboo
Dim wbk2 As Workboo
Dim IncludeSheet As Strin
Dim DataSheet As Strin
Dim Number As Intege

Set wbk1 = ActiveWorkboo
Set wbk2 = Workbooks.Ad

wbk1.Activat
Number =

For CounterComp = 6 To 1

IncludeSheet = wbk1.Worksheets("Landscap
Labels").Cells(CounterComp, 31
DataSheet = wbk1.Worksheets("Landscape Labels").Cells(CounterComp
7

If IncludeSheet = True The
Sheets(DataSheet).COPY After:=wbk2.Sheets(Number
Number = Number +

Els
Number = Number +

End I

Next CounterCom

End Su


--
Message posted from http://www.ExcelForum.com

Copying multiple worksheets into a new workbook
 

Thanks guys for the suggestions, however, I'm still getting stuck afte
copying the first sheet to the new workbook. The error I get is tha
it is a "Runtime error '9'; Subscript is out of range" and i
hightlights:

Sheets(DataSheet).COPY After:=wbk2.Worksheet.Count


Any other suggestions..

--
Message posted from http://www.ExcelForum.com

Copying multiple worksheets into a new workbook
 

wbk2.Worksheets(wbk2.worksheet.count)

would be the way to refer to the last worksheet in wbk2

another problem would be if Datasheet does not contain a valid sheetname
for the activeworkbook

--
Regards,
Tom Ogilvy


"anhjan " wrote in message
...
Thanks guys for the suggestions, however, I'm still getting stuck after
copying the first sheet to the new workbook. The error I get is that
it is a "Runtime error '9'; Subscript is out of range" and it
hightlights:

Sheets(DataSheet).COPY After:=wbk2.Worksheet.Count


Any other suggestions...


---
Message posted from http://www.ExcelForum.com/

Copying multiple worksheets into a new workbook
 

Typo:
Should be an s on both worksheets


wbk2.Worksheets(wbk2.worksheets.count)


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...

wbk2.Worksheets(wbk2.worksheet.count)

would be the way to refer to the last worksheet in wbk2

another problem would be if Datasheet does not contain a valid sheetname
for the activeworkbook

--
Regards,
Tom Ogilvy


"anhjan " wrote in message
...
Thanks guys for the suggestions, however, I'm still getting stuck after
copying the first sheet to the new workbook. The error I get is that
it is a "Runtime error '9'; Subscript is out of range" and it
hightlights:

Sheets(DataSheet).COPY After:=wbk2.Worksheet.Count


Any other suggestions...


---
Message posted from http://www.ExcelForum.com/