Copying Sheets to New Workbook

[cameron.scrimgeour]

I have used the following code to copy a sheet to a new workbook...

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="NewSheet"
ActiveWorkbook.Close

It works fine but I also have a Sheet2 which I want to copy into the same Workbook. Can this be done easily?

 

[hgus393]

I have used the following code to copy a sheet to a new workbook...

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="NewSheet"
ActiveWorkbook.Close

It works fine but I also have a Sheet2 which I want to copy into the same Workbook. Can this be done easily?

Hi to start with this is an Access forum, enough said!

I think this might be a clue:

[COLOR=#b1b100]Sub[/COLOR] CreateNewWorkbookForeverysheet[COLOR=#006600][B]([/B][/COLOR][COLOR=#006600][B])[/B][/COLOR] 
[COLOR=#b1b100]Dim[/COLOR] i [COLOR=#b1b100]As[/COLOR] [COLOR=#b1b100]Integer[/COLOR]
[COLOR=#b1b100]Dim[/COLOR] strSheetname [COLOR=#b1b100]As[/COLOR] [COLOR=#b1b100]String[/COLOR]
[COLOR=#b1b100]Dim[/COLOR] strPath [COLOR=#b1b100]As[/COLOR] [COLOR=#b1b100]String[/COLOR]
[COLOR=#b1b100]Dim[/COLOR] FileName [COLOR=#b1b100]As[/COLOR] [COLOR=#b1b100]String[/COLOR]
[COLOR=#b1b100]For[/COLOR] i = [COLOR=#cc66cc]2[/COLOR] [COLOR=#b1b100]To[/COLOR] Sheets.[COLOR=#b1b100]Count[/COLOR] 
Sheets[COLOR=#006600][B]([/B][/COLOR]i[COLOR=#006600][B])[/B][/COLOR].[COLOR=#66cc66]Activate[/COLOR]  strSheetname = ActiveCell.[COLOR=#66cc66]Worksheet[/COLOR].[COLOR=#b1b100]Name[/COLOR]
Sheets[COLOR=#006600][B]([/B][/COLOR]strSheetname [COLOR=#006600][B])[/B][/COLOR].[COLOR=#66cc66]Select[/COLOR]
[COLOR=#808080]'Copies every sheet to new workbook[/COLOR]
Sheets[COLOR=#006600][B]([/B][/COLOR]strSheetname [COLOR=#006600][B])[/B][/COLOR].[COLOR=#66cc66]Copy[/COLOR]
strPath = ThisWorkbook.[COLOR=#66cc66]Path[/COLOR]
[COLOR=#b1b100]ChDir[/COLOR] strPath 
FileName = strSheetname 
[COLOR=#808080]'Saves the new workbook in the same place as original file[/COLOR]
ActiveWorkbook.[COLOR=#66cc66]SaveAs[/COLOR] Filename:=strPath & [COLOR=#ff0000]""[/COLOR] & FileName , _
FileFormat:=xlNormal, Password:=[COLOR=#ff0000]""[/COLOR], WriteResPassword:=[COLOR=#ff0000]""[/COLOR], _
ReadOnlyRecommended:=[COLOR=#b1b100]False[/COLOR], CreateBackup:=[COLOR=#b1b100]False[/COLOR]
ActiveWindow.[COLOR=#b1b100]Close[/COLOR] Savechanges:=[COLOR=#b1b100]True[/COLOR]
[COLOR=#b1b100]Next[/COLOR]
[COLOR=#b1b100]End[/COLOR] [COLOR=#b1b100]Sub[/COLOR]

 

[joh024]

I have used the following code to copy a sheet to a new workbook...

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="NewSheet"
ActiveWorkbook.Close

It works fine but I also have a Sheet2 which I want to copy into the same Workbook. Can this be done easily?

In Excell things like this are easy. Just start recording a macro, do what you want, stop it, then look at the code.

FYI,
If you are trying to manipulate Excel from Access create the macro within excell then just call to that macro from access using an excel object.
-TJ