MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Moving between files in VBA during a macro....


Posted by Chris on December 10, 2001 10:21 AM

I need to copy and paste (as values) data from one workbook (File One) to another workbook that does not exist.

I have code that will do this for one worksheet in File One, but what I need to do is repeat this process for several sheets in File One. Here's what I need the macro to do:
Select a sheet in File One.
Select all cells, Copy.
Create a new workbook.
Select A1 and Paste Special Values, followed by Paste Special Formats.
Rename Sheet 1 to "Summary Sheet"
File / Save As, save newly-created workbook as "File Two".
-----I can do all of the above, here's where I get stuck.
Select File One.
Select second sheet to be copied, copy all cells.
Select File Two, insert Worksheet.
In new worksheet, select A1 and paste Special Values, followed by paste special Formats...etc, etc....

I don't know how to switch between the workbooks once File Two is created.

AND, I want to be able to do this no matter what filename is used, so I don't want to hardcode the filename into the macro. I'll need to declare a variable, I think.

Can anyone help me?


Posted by Dan on December 10, 2001 10:53 AM

Yes, you will need to declare a variable. Can you post the current code that you have? It is much easier to modify existing code than to create code from scratch.

Posted by Chris on December 10, 2001 11:27 AM

Here's my code (most generated from the Macro Recorder) (and I am already in File One)
Sheets("Summary Sheet").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Sheets("Sheet1").Name = "Summary Sheet"
Range("A1").Select
ActiveWorkbook.SaveAs Filename:=_
"C:\My Documents\Test\" & VariableName & ".xls"

I want the filename of File Two to be the same as File One, just in a different Directory (if possible).


Posted by Dan on December 10, 2001 12:45 PM

Ok. Sometimes it IS easier to write the code from scratch!! :) This code copies all sheets in FileOne over to FileTwo. It doesn't matter what the file name is, or number of sheets. I only ran in to a problem at the end when saving, because you can not have two open worbooks in Excel with the same filename, even if they are in different directories. I instead added the word "Copy" to the new workbook name (e.g. FileOne.xls and CopyFileOne.xls). Hope that helps.


Sub countWorksheets()

Dim WorkbookName1, WorkbookName2, SheetName As String
Dim NumSheets1, NumSheets2 As Integer

WorkbookName1 = ActiveWorkbook.Name
NumSheets1 = ActiveWorkbook.Sheets.Count
Workbooks.Add
WorkbookName2 = ActiveWorkbook.Name
NumSheets2 = ActiveWorkbook.Sheets.Count
'Sets the names of the workbooks to variables
'and counts number of sheets in each workbook

Do While NumSheets2 < NumSheets1
Worksheets.Add
NumSheets2 = ActiveWorkbook.Sheets.Count
Loop
'The section above adds the necessary
'amount of worksheets to the new workbook

Windows(WorkbookName1).Activate
For I = 1 To ActiveWorkbook.Sheets.Count
Windows(WorkbookName1).Activate
ActiveWorkbook.Sheets(I).Select
SheetName = ActiveWorkbook.Sheets(I).Name
Cells.Select
Selection.Copy
Windows(WorkbookName2).Activate
ActiveWorkbook.Sheets(I).Select
Cells.Select
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
ActiveWorkbook.Sheets(I).Name = SheetName
Next I
'The section above copies each sheet to
'the new workbook

Windows(WorkbookName2).Activate
ActiveWorkbook.SaveAs FileName:="c:\Copy" & WorkbookName1
'This saves the new workbook to the above folder
'And adds the word "Copy" to the front of the workbook name


End Sub

Posted by Bariloche on December 10, 2001 6:09 PM

A simplification (I hope)

David,

I haven't read all of Dan's response so I hope this isn't way off base. The following code snippets should do what you want:

Set wkbkFile1 = ActiveWorkbook
strFile1Name = ActiveWorkbook.Name
Sheets.Copy
wkbkFile1.Close
Set wkbkFile2 = ActiveWorkbook
strFile2Name = ActiveWorkbook.Name


Believe it or not but the code line "Sheets.Copy" copies the entire workbook. Its probably the most elegant object.method in all of Excel! LOL


The rest of that stuff creates variables to store your file names, like Dan advised.

Then, to do the PasteValues bit with the new workbook, you could use this:

For Each sht In Sheets

sht.Cells.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("A1").Select

Next sht


I'd also advise you to pick up a book on Excel VBA programming (like John Walkenbach's), it covers lots of these kinds of topics.


Hope this helps some.

enjoy

Sub countWorksheets() Dim NumSheets1, NumSheets2 As Integer NumSheets1 = ActiveWorkbook.Sheets.Count Workbooks.Add WorkbookName2 = ActiveWorkbook.Name NumSheets2 = ActiveWorkbook.Sheets.Count 'Sets the names of the workbooks to variables 'and counts number of sheets in each workbook Worksheets.Add NumSheets2 = ActiveWorkbook.Sheets.Count Loop 'The section above adds the necessary 'amount of worksheets to the new workbook For I = 1 To ActiveWorkbook.Sheets.Count Windows(WorkbookName1).Activate ActiveWorkbook.Sheets(I).Select SheetName = ActiveWorkbook.Sheets(I).Name Cells.Select Selection.Copy Windows(WorkbookName2).Activate ActiveWorkbook.Sheets(I).Select Cells.Select Selection.PasteSpecial Paste:=xlValues Selection.PasteSpecial Paste:=xlFormats ActiveWorkbook.Sheets(I).Name = SheetName Next I 'The section above copies each sheet to 'the new workbook ActiveWorkbook.SaveAs FileName:="c:\Copy" & WorkbookName1 'This saves the new workbook to the above folder 'And adds the word "Copy" to the front of the workbook name End Sub

Posted by Bariloche on December 10, 2001 6:14 PM

Oops, I mean "Chris" not "David"

Sorry about that Chris. I was reading two posts at once and got mixed up. :-))

David, I haven't read all of Dan's response so I hope this isn't way off base. The following code snippets should do what you want: Set wkbkFile1 = ActiveWorkbook strFile1Name = ActiveWorkbook.Name Sheets.Copy wkbkFile1.Close Set wkbkFile2 = ActiveWorkbook strFile2Name = ActiveWorkbook.Name Believe it or not but the code line "Sheets.Copy" copies the entire workbook. Its probably the most elegant object.method in all of Excel! LOL The rest of that stuff creates variables to store your file names, like Dan advised. Then, to do the PasteValues bit with the new workbook, you could use this: For Each sht In Sheets sht.Cells.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Range("A1").Select Next sht

I'd also advise you to pick up a book on Excel VBA programming (like John Walkenbach's), it covers lots of these kinds of topics. Hope this helps some.

enjoy

: Ok. Sometimes it IS easier to write the code from scratch!! :) This code copies all sheets in FileOne over to FileTwo. It doesn't matter what the file name is, or number of sheets. I only ran in to a problem at the end when saving, because you can not have two open worbooks in Excel with the same filename, even if they are in different directories. I instead added the word "Copy" to the new workbook name (e.g. FileOne.xls and CopyFileOne.xls). Hope that helps. : : Sub countWorksheets() : Dim WorkbookName1, WorkbookName2, SheetName As String : Dim NumSheets1, NumSheets2 As Integer : WorkbookName1 = ActiveWorkbook.Name : NumSheets1 = ActiveWorkbook.Sheets.Count : Workbooks.Add : WorkbookName2 = ActiveWorkbook.Name : NumSheets2 = ActiveWorkbook.Sheets.Count : 'Sets the names of the workbooks to variables : 'and counts number of sheets in each workbook : Do While NumSheets2 < NumSheets1 : Worksheets.Add : NumSheets2 = ActiveWorkbook.Sheets.Count : Loop : 'The section above adds the necessary : 'amount of worksheets to the new workbook : Windows(WorkbookName1).Activate : For I = 1 To ActiveWorkbook.Sheets.Count : Windows(WorkbookName1).Activate : ActiveWorkbook.Sheets(I).Select : SheetName = ActiveWorkbook.Sheets(I).Name : Cells.Select : Selection.Copy : Windows(WorkbookName2).Activate : ActiveWorkbook.Sheets(I).Select : Cells.Select : Selection.PasteSpecial Paste:=xlValues : Selection.PasteSpecial Paste:=xlFormats : ActiveWorkbook.Sheets(I).Name = SheetName : Next I : 'The section above copies each sheet to : 'the new workbook : Windows(WorkbookName2).Activate : ActiveWorkbook.SaveAs FileName:="c:\Copy" & WorkbookName1 : 'This saves the new workbook to the above folder : 'And adds the word "Copy" to the front of the workbook name : : End Sub