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

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.

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).

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

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

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