If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
need to fix file title in macro
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/ |
Thread Tools | |
Display Modes | |
|
|