Using a macro to pull data from a spreadsheet that changes its name daily

[shina67]

Hi All,

I have recorded a macro to pull data from various different spreadsheets to help determine throughput capacities.
The issue I have is that one of the spreadsheets changes name daily.
The line in the macro that this refers to is:-
Windows("Express Schedule 5-11am.xlsm").Activate

My question is how to I get the macro to pull the data from example "Express Schedule 6-11am.xlsm" and then "Express Schedule 7-11am.xlsm" as the new spreadsheet is created.

Hope this makes sense and thanks in advance for any help.

 

[jmacleary]

Re: Using a macro to pull data from a spreadsheet that changes it's name daily

Hello there. If the date will always be today's date, this will work. Replace the line Windows("Express Schedule 5-11am.xlsm").Activate with:

datebit = Format(Date, "d-mm")
fname = "Express Schedule " + datebit + "am.xlsm"
Windows(fname).Activate

 

[shina67]

Re: Using a macro to pull data from a spreadsheet that changes it's name daily

Hi, Thanks for that. However, I forgot to mention that the "am" part of the spreadsheet name could also be pm. The date will always be today's date

Hello there. If the date will always be today's date, this will work. Replace the line Windows("Express Schedule 5-11am.xlsm").Activate with:

datebit = Format(Date, "d-mm")
fname = "Express Schedule " + datebit + "am.xlsm"
Windows(fname).Activate

 

[jmacleary]

Re: Using a macro to pull data from a spreadsheet that changes it's name daily

I thought it might be. Use these 2 lines:

datebit = Format(Date, "d-mmam/pm")
fname = "Express Schedule " + datebit + ".xlsm"