Macro for adding data from another file

[Tucker92]

Hi there. Very new to VBA and macros. Not sure what im doing.
At the moment i have recorded a macro that pulls information in from another excel spread sheet and sorts it. But a new spread sheet is brought out every week with the same name except from two numbers in it.
Is there anyway i could have it so it updates the number itself so that i don't have to edit the macro each week. Or can i make another macro that updates it before i use it?

Thank you

 

[Peter_SSs]

Is there anyway i could have it so it updates the number itself so that i don't have to edit the macro each week.

Possibly, but without seeing the macro or knowing how "the number" should be updated it is hard to offer any specific advice.

 

[Tucker92]

at the moment i am opening the workbook and copying the data out of it using my macro
here is a part of my macro and the big bold numbers are the ones that i have been changing each week to the next number.

Workbooks.Open Filename:= _
"G:\All discrete jobs for week\All discrete jobs for week 33 2018.xlsx"
Range("C1").Select
ActiveSheet.Range("$A$1:$K$5000").AutoFilter Field:=3, Criteria1:= _
"S/C WK 33-2018"
Range("A2:E5000").Select
Selection.Copy

 

[Peter_SSs]

So, is there some way to calculate that week number (perhaps from the current date?) or is it just that every time the macro is run, that number is incremented by 1?

Do we have, or could we have, in the workbook where this code resides a cell (what sheet?) that records what the current number is and then we can just increment that number both in the code and in the cell that records it?

 

[Tucker92]

The week is based around the week in the year. it is currently the 34th week this year.
the spreadsheet that is released is two weeks ahead so they are currently working on week 36.
I have created the cell to show the current week and the week that i need but don't know how to link that into my macro.

 

[Peter_SSs]

I have created the cell to show ... the week that i need but don't know how to link that into my macro.

What sheet and what cell is that?

 

[Tucker92]

The sheet is called "WTL Progress" and the cell is "F3"

 

[Peter_SSs]

The sheet is called "WTL Progress" and the cell is "F3"

Then try (untested)

Dim WeekNum As Long

WeekNum = Sheets("WTL Progress").Range("F3").Value
Workbooks.Open Filename:= _
"G:\All discrete jobs for week\All discrete jobs for week " & WeekNum & " 2018.xlsx"
Range("C1").Select
ActiveSheet.Range("$A$1:$K$5000").AutoFilter Field:=3, Criteria1:="S/C WK " & WeekNum & "-2018"
Range("A2:E5000").Select
Selection.Copy

 

[Tucker92]

Thank you
It took a little bit of tweaking my spreadsheet and the rest of the macro but i have now got it to work. thank you for your help.
Is there any way i could gather the information i want from the other spreadsheet without it having to open the file??

 

[Peter_SSs]

Thank you
It took a little bit of tweaking my spreadsheet and the rest of the macro but i have now got it to work. thank you for your help.

You're welcome.

Is there any way i could gather the information i want from the other spreadsheet without it having to open the file??

That's not an area I am too familiar with. I'd suggest searching the forum for topics about that and if you cannot find what you need, I would start a new thread specifically about extracting data from a closed file.