Macro expertise needed - please help!

[Warrior33]

Hi guys,

I hope you are well. I’m currently working in constructing a macro and needed your help.

The purpose of the macro is to copy and paste data from an open spreadsheet to another open spreadsheet.

I recorded a macro using a spreadsheet named “TEST X 080819” and pasted data to a spreadsheet called “AUGUST TRACKER” and here comes the problem: the file TEST X changes name every day because of the date (tomorrow it will be TEST X 090819) and the tracker will have its name changed in September.

So the question is: how do I edit the code so as to catch the file names correctly without having to edit the Visual Basic?

Any recommendations? Thanks a lot!

 

[chirayu]

So you're pasting from "TEST X DDMMYY" to "AUGUST TRACKER"?

For one if you add your code to Personal.xlsb it means it doesn't need to be added to TEST

After that whenever you run the macro, make sure you're in TEST before running it and store the filename as a variable

In terms of pasting it to the correct tracker variable from TEST filename to define a variable for month to get relevant tracker details

Example code below

Sub CopyPaster()

Dim CopyFile As String
Dim CopyDate As String
Dim MyDay As String
Dim MyMonth As String
Dim MyYear As String
Dim MonthName As String
Dim PasteFile As String

CopyFile = ActiveWorkbook.Name
CopyDate = Right(Split(CopyFile, ".")(0), 6)
MyDay = Left(CopyDate, 2)
MyMonth = Mid(CopyDate, 3, 2)
MyYear = Right(CopyDate, 2)
MonthName = Format(DateSerial(MyYear, MyMonth, MyDay), "MMMM")
PasteFile = MonthName & " Tracker.xlsx"

'Your Copy Paste code here

End Sub

 

[vletm]

Warrior33
I should start asking 'Why copy...?, 'How about next year ... there will be August too? ... and so on
You're writing copy&paste from sheet to sheet ... the file TEST X ....
If someone will miss one day ... how to handle ... if something automatic?
It would be more clear to send some sample file with sample datas ... with needed result.

 

[Marc L]

Hi !​

So the question is: how do I edit the code so as to catch the file names correctly without having to edit the Visual Basic?

Easy : select the file via the GetOpenFilename method for example ... (to see in VBA inner help)​

​