ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with activating window from Application.GetOpenFilename (https://www.excelbanter.com/excel-programming/442282-help-activating-window-application-getopenfilename.html)

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

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



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


.

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


All times are GMT +1. The time now is 08:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com