Hi everyone
Please bear with me, as I am new to macros.
I have created a spreadsheet (currently just a test/example) that I will send out via email to staff members.
They will input their information (eg. name, job title, phone number etc) and email it back (so there will be variable file names).
When I receive the file back from them, I will open that and run a macro from the master spreadsheet that I will copy the information into.
This is the macro I have so far... (this is just me testing, I haven't fully developed all the content, hence the simplicity)...
Sub Copy_data()
'
' Copy_data Macro
' copies data from any other open workbook into this workbook
'
'
Windows("Jess.xls").Activate
Range("B1").Select
Selection.Copy
Windows("Master.xls").Activate
ActiveSheet.Paste
Range("B2").Select
Windows("Jess.xls").Activate
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master.xls").Activate
ActiveSheet.Paste
Range("C2").Select
Windows("Jess.xls").Activate
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master.xls").Activate
ActiveSheet.Paste
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
End Sub
The problem is, when I receive another file and open it eg. "John.xls" (instead of "Jess.xls"), I try to run the macro, it doesn't work because it is a different file name.
Is there something I can replace in the line:
Windows("Jess.xls").Activate
that will allow me to use whatever the other file name is open (whether it be "Jess.xls", "John.xls", "Mary.xls", "Peter.xls" etc etc) and import the data from that file?
The "master.xls" is the file that contains the macro that will house all the data.
I have attached the example master file and two test files that people might send to me.
Master.xlsJess.xlsJohn.xls
Thanks in advance!
Jess.
Bookmarks