Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hi,
I have a workbook with about 6 worksheets on it. I only want to copy 4 of those worksheets to a new workbook but, not all of the worksheets exist. The following code works. Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Select Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Copy If I build a string with only the sheets I want to copy I get a "Subscript out of range" error. worksheet_exists = "" Set worksheet_exists = Sheets("Accounts") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Accounts" End If End If Set worksheet_exists = Sheets("Expenses") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Expenses" Else worksheet_to_copy = worksheet_to_copy & """, ""Expenses" End If End If Set worksheet_exists = Sheets("Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""Tax" End If End If Set worksheet_exists = Sheets("September Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "September Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""September Tax" End If End If ' This gives me an error - Subscript out of range Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hi,
Rather that looking for the sheets to copy, why not look for the sheets not to copy? dim intNum, intCount as integer dim strSheetNamer1, shtName2 as string strSheetName1="Not This One" strSheetName2="Or This One" intCount=sheets.count for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ then sheets(intNum).Copy 'to whereever end if next intnum "munt via OfficeKB.com" wrote: Hi, I have a workbook with about 6 worksheets on it. I only want to copy 4 of those worksheets to a new workbook but, not all of the worksheets exist. The following code works. Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Select Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Copy If I build a string with only the sheets I want to copy I get a "Subscript out of range" error. worksheet_exists = "" Set worksheet_exists = Sheets("Accounts") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Accounts" End If End If Set worksheet_exists = Sheets("Expenses") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Expenses" Else worksheet_to_copy = worksheet_to_copy & """, ""Expenses" End If End If Set worksheet_exists = Sheets("Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""Tax" End If End If Set worksheet_exists = Sheets("September Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "September Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""September Tax" End If End If ' This gives me an error - Subscript out of range Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hi Exceluserforeman,
for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want I think that you would need to iterate though the entire sheets collection, unless you know that the two sheets in question are the last two sheets. Of course, if this were the case, there would be no need to effect the name comparisons: if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ --- Regards, Norman "exceluserforeman" wrote in message ... Hi, Rather that looking for the sheets to copy, why not look for the sheets not to copy? dim intNum, intCount as integer dim strSheetNamer1, shtName2 as string strSheetName1="Not This One" strSheetName2="Or This One" intCount=sheets.count for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ then sheets(intNum).Copy 'to whereever end if next intnum "munt via OfficeKB.com" wrote: Hi, I have a workbook with about 6 worksheets on it. I only want to copy 4 of those worksheets to a new workbook but, not all of the worksheets exist. The following code works. Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Select Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Copy If I build a string with only the sheets I want to copy I get a "Subscript out of range" error. worksheet_exists = "" Set worksheet_exists = Sheets("Accounts") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Accounts" End If End If Set worksheet_exists = Sheets("Expenses") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Expenses" Else worksheet_to_copy = worksheet_to_copy & """, ""Expenses" End If End If Set worksheet_exists = Sheets("Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""Tax" End If End If Set worksheet_exists = Sheets("September Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "September Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""September Tax" End If End If ' This gives me an error - Subscript out of range Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hi,
Thanks for that - it almost worked. It's now copying each spreadsheet to a seperate workbook whereas I wanted them in one workbook. exceluserforeman wrote: Hi, Rather that looking for the sheets to copy, why not look for the sheets not to copy? dim intNum, intCount as integer dim strSheetNamer1, shtName2 as string strSheetName1="Not This One" strSheetName2="Or This One" intCount=sheets.count for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ then sheets(intNum).Copy 'to whereever end if next intnum Hi, [quoted text clipped - 51 lines] Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hello Norman,
Yes, I think you are right. I only tested it when the two unwanted sheets were at the end of the collection. Thanks for making that point. I think it would be best to put in a blank Else statement so that the macro has an option if the statement is false. - -mark "Norman Jones" wrote: Hi Exceluserforeman, for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want I think that you would need to iterate though the entire sheets collection, unless you know that the two sheets in question are the last two sheets. Of course, if this were the case, there would be no need to effect the name comparisons: if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ --- Regards, Norman "exceluserforeman" wrote in message ... Hi, Rather that looking for the sheets to copy, why not look for the sheets not to copy? dim intNum, intCount as integer dim strSheetNamer1, shtName2 as string strSheetName1="Not This One" strSheetName2="Or This One" intCount=sheets.count for intNum=1 to intCount-2 'minus 2 because of those 2 you do not want if sheets(intNum) .name<strSheetName1 or _ sheets(intNum) .name<strSheetName2 _ then sheets(intNum).Copy 'to whereever end if next intnum "munt via OfficeKB.com" wrote: Hi, I have a workbook with about 6 worksheets on it. I only want to copy 4 of those worksheets to a new workbook but, not all of the worksheets exist. The following code works. Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Select Sheets(Array("Accounts", "Expenses", "Tax", "September Tax")).Copy If I build a string with only the sheets I want to copy I get a "Subscript out of range" error. worksheet_exists = "" Set worksheet_exists = Sheets("Accounts") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Accounts" End If End If Set worksheet_exists = Sheets("Expenses") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Expenses" Else worksheet_to_copy = worksheet_to_copy & """, ""Expenses" End If End If Set worksheet_exists = Sheets("Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""Tax" End If End If Set worksheet_exists = Sheets("September Tax") If worksheet_exists Is Nothing Then Else If worksheet_to_copy = "" Then worksheet_to_copy = "September Tax" Else worksheet_to_copy = worksheet_to_copy & """, ""September Tax" End If End If ' This gives me an error - Subscript out of range Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy only some existing worksheets to a new workbook
Hi Mark,
Thanks for all your help. When I copied the code into my Macro I found the problem Norman identified. Anyway as I said before it worked but copied each sheet to a seperate workbook. I have now changed tack and copied ALL the sheets to a new workbook I then use your example to read through the sheets and delete the ones I don't want. Thanks heaps for pointing me in a different direction, I was so focused on copying only the sheets I wanted and trying to resolve my error that I didn't look at other options. exceluserforeman wrote: Hello Norman, Yes, I think you are right. I only tested it when the two unwanted sheets were at the end of the collection. Thanks for making that point. I think it would be best to put in a blank Else statement so that the macro has an option if the statement is false. - -mark Hi Exceluserforeman, [quoted text clipped - 93 lines] Sheets(Array(worksheet_to_copy)).Select Sheets(Array(worksheet_to_copy)).Copy -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Existing Worksheets into a new Workbook | New Users to Excel | |||
ADDING MORE WORKSHEETS TO AN EXISTING WORKBOOK | Excel Discussion (Misc queries) | |||
ADDING MORE WORKSHEETS TO AN EXISTING WORKBOOK | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy Rows to Existing Worksheets | Excel Programming |