Help with activating window from Application.GetOpenFilename
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
.
|