leezac
asked on
Save Sheet into another Workbook
This should be really simple but I have having problems.
I recorded a macro, but am not able to run this code. I developed in 2010 but also need to run in 2003. I have tried to find examples but people seem to use different ways to do this. If someone could look at my code, I would appreciate it.
I am just needing to copy Sheet 1, copy pastevalues and save to another workbook.
The problem areas or Cells.select and Activeworkbook.Saveas
Sheets("Sheet1").Activate
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="H:\Employess " & Format(Now, "yyyy-mm-dd hh mm") & ".xls"
Application.WindowState = xlMinimized
I recorded a macro, but am not able to run this code. I developed in 2010 but also need to run in 2003. I have tried to find examples but people seem to use different ways to do this. If someone could look at my code, I would appreciate it.
I am just needing to copy Sheet 1, copy pastevalues and save to another workbook.
The problem areas or Cells.select and Activeworkbook.Saveas
Sheets("Sheet1").Activate
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="H:\Employess " & Format(Now, "yyyy-mm-dd hh mm") & ".xls"
Application.WindowState = xlMinimized
Michael Machie
I do not know how to do this but should inform you that if you used any features contained within 2010 to build this and those features will need to be available in 2003 to use.
You can copy the entire sheet without selecting the cells.
This moves the worksheet into a new workbook.
Sheets("Sheet1").Copy
If you want to keep the sheet in the original workbook also, then use
Dim wb As Workbook, wbNew As Workbook
Set wb = ThisWorkbook
Set wbNew = Workbooks.Add
wb.Sheets("Sheet1").Copy Before:=wbNew.Sheets(1)
Don't put the extension ".xls" into the filename when saving.
Use Fileformat to set the filetype
wbNew.SaveAs Filename:="H:\Employess " & Format(Now, "yyyy-mm-dd hh mm") , Fileformat:=Excel8
Excel8 means Excel 2003 format.
This moves the worksheet into a new workbook.
Sheets("Sheet1").Copy
If you want to keep the sheet in the original workbook also, then use
Dim wb As Workbook, wbNew As Workbook
Set wb = ThisWorkbook
Set wbNew = Workbooks.Add
wb.Sheets("Sheet1").Copy Before:=wbNew.Sheets(1)
Don't put the extension ".xls" into the filename when saving.
Use Fileformat to set the filetype
wbNew.SaveAs Filename:="H:\Employess " & Format(Now, "yyyy-mm-dd hh mm") , Fileformat:=Excel8
Excel8 means Excel 2003 format.
ASKER
hgholt - I do not need to save sheet to same workbook, and need to open the saved file on H:\ in both 2003 and 2010 so don't think Excel8 can be used for both 2003 and 2010. I also need to paste special values into new sheet. I am a little confused how the code should look.
ASKER
This works in 2010, but not sure how will work in 2003 and I will try with taking the .xls out of the code to save.
sheets("sheet1").activate
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="P:\test\Em[;pue
sheets("sheet1").activate
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="P:\test\Em[;pue
In Excel 2003 the FileFormat xlExcel8 has no meaning, it is called Normal.
Make 2 SaveAs statements, If .. Then .. Else
For Excel 2003 without FileFormat specification, since it will automatically save as xls.
For Excel 2010 with FileFormat:=xlExcel8, meaning save as xls.
Use Left(Application.Version,2
For me the version value for Excel 2003 is "11.0", but I don't know if ".0" can be something different, so just check for "11".
If you want your cell formats into the new sheet, you could copy the entire sheet as mentioned above, and then change all formulas to values with the PasteSpecial operation on the new sheet.
Make 2 SaveAs statements, If .. Then .. Else
For Excel 2003 without FileFormat specification, since it will automatically save as xls.
For Excel 2010 with FileFormat:=xlExcel8, meaning save as xls.
Use Left(Application.Version,2
For me the version value for Excel 2003 is "11.0", but I don't know if ".0" can be something different, so just check for "11".
If you want your cell formats into the new sheet, you could copy the entire sheet as mentioned above, and then change all formulas to values with the PasteSpecial operation on the new sheet.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.