I am new to VBA and I have spent hours trying to solve the following macro.

Currently, this code works how I want it except I would like to change the
of the file names of "Book1.xls" and "Book2.xls" to file paths for variables
"sFile1" and "dFile".

For example, if I selected "Book4.xls" and "Book5.xls", I would like the
activewindow to be reflected these file paths instead.

thanks for any help!

Curt J

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub

I'd use something like:

Option Explicit
Sub CopyPaste()

Dim sFileName As Variant
Dim dFileName As Variant
Dim sFile As Workbook
Dim dFile As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

sFileName = Application.GetOpenFilename
If sFileName = False Then
'user hit cancel
Exit Sub
End If

dFileName = Application.GetOpenFilename
If dFileName = False Then
Exit Sub
End If

Set sFile = Workbooks.Open(sFileName)
Set dFile = Workbooks.Open(dFileName)

'the entire sheet
Set RngToCopy = sFile.Worksheets("sheet1").Cells
'let excel resize as necessary
Set DestCell = dFile.Worksheets("sheet1").Range("A1")

RngToCopy.Copy _
Destination:=DestCell

Set RngToCopy = sFile.Worksheets("sheet2").Cells
Set DestCell = dFile.Worksheets("sheet2").Range("A1")

RngToCopy.Copy _
Destination:=DestCell

'and maybe...
sFile.Close savechanges:=False
dFile.Close savechanges:=True

End Sub


========
You could even loop through the worksheets if you wanted:

Option Explicit
Sub CopyPaste()

Dim sFileName As Variant
Dim dFileName As Variant
Dim sFile As Workbook
Dim dFile As Workbook
Dim RngToCopy As Range
Dim DestCell As Range
Dim wCtr As Long

sFileName = Application.GetOpenFilename
If sFileName = False Then
'user hit cancel
Exit Sub
End If

dFileName = Application.GetOpenFilename
If dFileName = False Then
Exit Sub
End If

Set sFile = Workbooks.Open(sFileName)
Set dFile = Workbooks.Open(dFileName)

For wCtr = 1 To 2
'the entire sheet
Set RngToCopy = sFile.Worksheets("sheet" & wCtr).Cells
'let excel resize as necessary
Set DestCell = dFile.Worksheets("sheet" & wCtr).Range("A1")

RngToCopy.Copy _
Destination:=DestCell
Next wCtr

sFile.Close savechanges:=False
dFile.Close savechanges:=True

End Sub

Curt wrote:

I am new to VBA and I have spent hours trying to solve the following macro.

Currently, this code works how I want it except I would like to change the
of the file names of "Book1.xls" and "Book2.xls" to file paths for variables
"sFile1" and "dFile".

For example, if I selected "Book4.xls" and "Book5.xls", I would like the
activewindow to be reflected these file paths instead.

thanks for any help!

Curt J

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub

--

Dave Peterson

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

sFile.Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

dFile.Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

sFile.Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

dFile.Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub





"Curt" wrote in message
...
I am new to VBA and I have spent hours trying to solve the following macro.

Currently, this code works how I want it except I would like to change the
of the file names of "Book1.xls" and "Book2.xls" to file paths for
variables
"sFile1" and "dFile".

For example, if I selected "Book4.xls" and "Book5.xls", I would like the
activewindow to be reflected these file paths instead.

thanks for any help!

Curt J

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub

thanks!

"JLGWhiz" wrote:

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

sFile.Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

dFile.Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

sFile.Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

dFile.Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub





"Curt" wrote in message
...
I am new to VBA and I have spent hours trying to solve the following macro.

Currently, this code works how I want it except I would like to change the
of the file names of "Book1.xls" and "Book2.xls" to file paths for
variables
"sFile1" and "dFile".

For example, if I selected "Book4.xls" and "Book5.xls", I would like the
activewindow to be reflected these file paths instead.

thanks for any help!

Curt J

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub


.

thanks this was great!

"Dave Peterson" wrote:

I'd use something like:

Option Explicit
Sub CopyPaste()

Dim sFileName As Variant
Dim dFileName As Variant
Dim sFile As Workbook
Dim dFile As Workbook
Dim RngToCopy As Range
Dim DestCell As Range

sFileName = Application.GetOpenFilename
If sFileName = False Then
'user hit cancel
Exit Sub
End If

dFileName = Application.GetOpenFilename
If dFileName = False Then
Exit Sub
End If

Set sFile = Workbooks.Open(sFileName)
Set dFile = Workbooks.Open(dFileName)

'the entire sheet
Set RngToCopy = sFile.Worksheets("sheet1").Cells
'let excel resize as necessary
Set DestCell = dFile.Worksheets("sheet1").Range("A1")

RngToCopy.Copy _
Destination:=DestCell

Set RngToCopy = sFile.Worksheets("sheet2").Cells
Set DestCell = dFile.Worksheets("sheet2").Range("A1")

RngToCopy.Copy _
Destination:=DestCell

'and maybe...
sFile.Close savechanges:=False
dFile.Close savechanges:=True

End Sub


========
You could even loop through the worksheets if you wanted:

Option Explicit
Sub CopyPaste()

Dim sFileName As Variant
Dim dFileName As Variant
Dim sFile As Workbook
Dim dFile As Workbook
Dim RngToCopy As Range
Dim DestCell As Range
Dim wCtr As Long

sFileName = Application.GetOpenFilename
If sFileName = False Then
'user hit cancel
Exit Sub
End If

dFileName = Application.GetOpenFilename
If dFileName = False Then
Exit Sub
End If

Set sFile = Workbooks.Open(sFileName)
Set dFile = Workbooks.Open(dFileName)

For wCtr = 1 To 2
'the entire sheet
Set RngToCopy = sFile.Worksheets("sheet" & wCtr).Cells
'let excel resize as necessary
Set DestCell = dFile.Worksheets("sheet" & wCtr).Range("A1")

RngToCopy.Copy _
Destination:=DestCell
Next wCtr

sFile.Close savechanges:=False
dFile.Close savechanges:=True

End Sub

Curt wrote:

I am new to VBA and I have spent hours trying to solve the following macro.

Currently, this code works how I want it except I would like to change the
of the file names of "Book1.xls" and "Book2.xls" to file paths for variables
"sFile1" and "dFile".

For example, if I selected "Book4.xls" and "Book5.xls", I would like the
activewindow to be reflected these file paths instead.

thanks for any help!

Curt J

Sub CopyPaste()

Dim sFile As Workbook
Set sFile = Workbooks.Open(Application.GetOpenFilename)


Dim dFile As Workbook
Set dFile = Workbooks.Open(Application.GetOpenFilename)

Windows("Book1.xls").Activate
Sheets("Sheet1").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Cells.Select
ActiveSheet.Paste

Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste


End Sub

--

Dave Peterson
.