Help with activating window from Application.GetOpenFilename

[Curt]

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]

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

 

[JLGWhiz]

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]

thanks!

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]

thanks this was great!

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