Importing data to Master File that has a changing name

Kieran1995

New Member
Joined
Mar 12, 2018
Messages
3
Hi,

I'm trying to copy and paste data from a selected workbook into my master file. One main problem I'm having is getting back to the master file, the reason being is that the name of the master file changes each month to whatever month it's being run in e.g. "March Master File" for this month "April Master File" when I run it next month. I have tried getting back to the file by referencing a dynamic file path but it doesn't work. Any suggestions on if there's an easier way of writing this and not having to refer to a file path?

This is what I got so far:

Code:
Sub ImportRepurchData()
    Dim combinedBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim combinedFilename As String
    Dim combinedWorkbook As Workbook
    Dim targetWorkbook As Workbook
    
    TEMPLATEFile = Sheets("Dynamic").Range("ImportRepurch")
    
    
' Open Source Data
    
    Set targetWorkbook = Application.ActiveWorkbook
    
    filter = "Text files (*.xlsx),*.xlsx"
    caption = "Please Select an input file"
    combinedFilename = Application.GetOpenFilename(filter, , caption)
    Set combinedWorkbook = Application.Workbooks.Open(combinedFilename)
    
' Paste Source Data into original workbook
    
    Range("P4:P100000,AL4:AL100000").Select
    Selection.Copy
    
    Windows(TEMPLATEFile).Activate
        
    Sheets("Input Sched-Unsched Split").Select
    Range("B9:B100000,C9:C100000").Select
    ActiveSheet.Paste
    
    combinedWorkbook.Close
    
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,114,445
Messages
5,547,962
Members
410,820
Latest member
Prepost
Top