GMC The Macro Man
Board Regular
- Joined
- Mar 23, 2023
- Messages
- 100
- Office Version
- 2021
- Platform
- Windows
Hi team.
I have successfully created a script that goes to a Folder Path and opens a file of your choosing from a multiple list which changes daily, so it won't be the same FNAME. The file has TWO Sheets, "SALES" and "BUDGET". For my report I copy all of the data from the selected sheet ("SALES") and then paste this in to the a chosen sheet within the MASTER file ("Current Sales").
Easy so far but now this is where I am stuck.
I have now been asked to copy the data from the other sheet ("BUDGET") from the above input file and copy all the data to another Sheet ("Current Budget") within the MASTER file.
The issue i have is after doing the 1st part successfully, i need to RETURN to the input file so I can do this task but as the input file will always be a different file, (it doesn't have a FIXED filename,) I don't know what command to write to tell it to go back the the input file.
The MASTER file is currently open and holds the MACRO which runs this script.
This is what's written for the 1st part
Sub Return_()
'Create Message Box
MsgBox "Select current Change Report", vbInformation
Fname = Application.GetOpenFilename(Title:="Select file", MultiSelect:=False)
'Debug.Print Fname
Dim wbTS As Workbook
Set wbTS = Workbooks.Open(Fname)
'Open worksheet tab Named "Sales" and copy all data
wbTS.Worksheets("Sales").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Sales").Select
Range("A1").Select
ActiveSheet.Paste
From this point, I need to loop it back to the input file and do the same task again for the BUDGET but I don't know what command i need to write here.
Any help would be greatly appreciated.
Thanks
GMC
I have successfully created a script that goes to a Folder Path and opens a file of your choosing from a multiple list which changes daily, so it won't be the same FNAME. The file has TWO Sheets, "SALES" and "BUDGET". For my report I copy all of the data from the selected sheet ("SALES") and then paste this in to the a chosen sheet within the MASTER file ("Current Sales").
Easy so far but now this is where I am stuck.
I have now been asked to copy the data from the other sheet ("BUDGET") from the above input file and copy all the data to another Sheet ("Current Budget") within the MASTER file.
The issue i have is after doing the 1st part successfully, i need to RETURN to the input file so I can do this task but as the input file will always be a different file, (it doesn't have a FIXED filename,) I don't know what command to write to tell it to go back the the input file.
The MASTER file is currently open and holds the MACRO which runs this script.
This is what's written for the 1st part
Sub Return_()
'Create Message Box
MsgBox "Select current Change Report", vbInformation
Fname = Application.GetOpenFilename(Title:="Select file", MultiSelect:=False)
'Debug.Print Fname
Dim wbTS As Workbook
Set wbTS = Workbooks.Open(Fname)
'Open worksheet tab Named "Sales" and copy all data
wbTS.Worksheets("Sales").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Sales").Select
Range("A1").Select
ActiveSheet.Paste
From this point, I need to loop it back to the input file and do the same task again for the BUDGET but I don't know what command i need to write here.
Any help would be greatly appreciated.
Thanks
GMC
Last edited: