• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro expertise needed - please help!

Warrior33

New Member
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

Well-Known Member
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

Code:
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

Excel Ninja
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

Excel Ninja
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)
 
Top