Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
As subject, Thanks
|
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
worksheets(Array("Sheet1","Sheet4")).Copy
Activeworkbook.SaveAs "C:\My Folder\abc.xls" -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... As subject, Thanks |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi, Tom
I don't know why the following code cannot work and with the error msg (Run-time error '9' Subscript out of range), can you help? For j = 5 To 99 dept_code = Right("0" & j, 2) For i = 1 To Worksheets.Count If Left(Worksheets(i).Name, 2) = dept_code Then temp_array = temp_array & Chr(34) & Worksheets(i).Name & Chr(34) & ", " End If Next i temp_array = Left(temp_array, Len(temp_array) - 2) If temp_array < "" Then Sheets(Array(temp_array)).Copy ActiveWorkbook.SaveAs Filename:= _ "C:\testing.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close End If temp_array = "" Next j "Tom Ogilvy" wrote in message ... worksheets(Array("Sheet1","Sheet4")).Copy Activeworkbook.SaveAs "C:\My Folder\abc.xls" -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... As subject, Thanks |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Because you can't construct an array that way (tried it years ago myself).
Dim bReplace as Boolean Dim i as Long, j as Long Dim dept_code = String For j = 5 To 99 dept_code = Right("0" & j, 2) bReplace = True For i = 1 To Worksheets.Count if If Left(Worksheets(i).Name, 2) = dept_code Then worksheets(i).Select bReplace bReplace = False end if Next i if Not bReplace then ActiveWindow.SelectedSheets.copy ActiveWorkbook.SaveAs Filename:= _ "C:\testing" & dept_code" & .xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close SaveChanges:=False End If Next j -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... Hi, Tom I don't know why the following code cannot work and with the error msg (Run-time error '9' Subscript out of range), can you help? For j = 5 To 99 dept_code = Right("0" & j, 2) For i = 1 To Worksheets.Count If Left(Worksheets(i).Name, 2) = dept_code Then temp_array = temp_array & Chr(34) & Worksheets(i).Name & Chr(34) & ", " End If Next i temp_array = Left(temp_array, Len(temp_array) - 2) If temp_array < "" Then Sheets(Array(temp_array)).Copy ActiveWorkbook.SaveAs Filename:= _ "C:\testing.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close End If temp_array = "" Next j "Tom Ogilvy" wrote in message ... worksheets(Array("Sheet1","Sheet4")).Copy Activeworkbook.SaveAs "C:\My Folder\abc.xls" -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... As subject, Thanks |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thank you very much Tom, it works perfectly.
"Tom Ogilvy" wrote in message ... Because you can't construct an array that way (tried it years ago myself). Dim bReplace as Boolean Dim i as Long, j as Long Dim dept_code = String For j = 5 To 99 dept_code = Right("0" & j, 2) bReplace = True For i = 1 To Worksheets.Count if If Left(Worksheets(i).Name, 2) = dept_code Then worksheets(i).Select bReplace bReplace = False end if Next i if Not bReplace then ActiveWindow.SelectedSheets.copy ActiveWorkbook.SaveAs Filename:= _ "C:\testing" & dept_code" & .xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close SaveChanges:=False End If Next j -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... Hi, Tom I don't know why the following code cannot work and with the error msg (Run-time error '9' Subscript out of range), can you help? For j = 5 To 99 dept_code = Right("0" & j, 2) For i = 1 To Worksheets.Count If Left(Worksheets(i).Name, 2) = dept_code Then temp_array = temp_array & Chr(34) & Worksheets(i).Name & Chr(34) & ", " End If Next i temp_array = Left(temp_array, Len(temp_array) - 2) If temp_array < "" Then Sheets(Array(temp_array)).Copy ActiveWorkbook.SaveAs Filename:= _ "C:\testing.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close End If temp_array = "" Next j "Tom Ogilvy" wrote in message ... worksheets(Array("Sheet1","Sheet4")).Copy Activeworkbook.SaveAs "C:\My Folder\abc.xls" -- Regards, Tom Ogilvy "new.microsoft.com" wrote in message ... As subject, Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
Application error when attempting to write code to a workbook | Excel Programming | |||
Copying sheets without code | Excel Programming | |||
Workbook Last Write Date Time | Excel Programming | |||
Write to closed workbook code | Excel Programming |