need to fix file title in macro

I have the following macro created which allows the user select a file
from which a worksheet is copied in to the 'report' sheet of the
macro-containing file. The problem is the following macro only runs
when the file is 'international breakdown month.xls' because that is
how it is written in the macro. (noted in blue below) However, I need
this macro to still be able to run if the file name were to change.
For example if in the month of June, it was saved as 'international
breakdown june.xls'. Is there a way to correct this macro so that the
macro copies the worksheet in to 'report' of whatever current
'international breakdown XXXX' file is open?

Sub GenerateReport()

' Open file
Dim myFile
Dim myFileName As String
myFile = Application.GetOpenFilename("All Files,*.*")
If myFile = False Then
Exit Sub
End If
Workbooks.Open FileName:=myFile
myFileName = ActiveWorkbook.Name

' Select cells to copy
Dim myInputRange As String
myInputRange = Range("A1:AG10000").Select

' Paste range to master sheet
Range("A1:AG10000").Copy
Windows ("International Breakdown Month.xls").
Activate
Sheets("Report").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

' Close file you opened
Windows(myFileName).Close False

End Sub


---
Message posted from http://www.ExcelForum.com/

One way,

ThisWorkbook.Activate

From Excel Help: ThisWorkbook returns a Workbook object that represents the
workbook where the current macro code is running. Read-only.

HTH
Anders Silven

"leeners82 " skrev i meddelandet
...
I have the following macro created which allows the user select a file
from which a worksheet is copied in to the 'report' sheet of the
macro-containing file. The problem is the following macro only runs
when the file is 'international breakdown month.xls' because that is
how it is written in the macro. (noted in blue below) However, I need
this macro to still be able to run if the file name were to change.
For example if in the month of June, it was saved as 'international
breakdown june.xls'. Is there a way to correct this macro so that the
macro copies the worksheet in to 'report' of whatever current
'international breakdown XXXX' file is open?

Sub GenerateReport()

' Open file
Dim myFile
Dim myFileName As String
myFile = Application.GetOpenFilename("All Files,*.*")
If myFile = False Then
Exit Sub
End If
Workbooks.Open FileName:=myFile
myFileName = ActiveWorkbook.Name

' Select cells to copy
Dim myInputRange As String
myInputRange = Range("A1:AG10000").Select

' Paste range to master sheet
Range("A1:AG10000").Copy
Windows ("International Breakdown Month.xls").
Activate
Sheets("Report").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

' Close file you opened
Windows(myFileName).Close False

End Sub


---
Message posted from http://www.ExcelForum.com/