CODEWITHSUNDEEP
vbaexcel
Lóránt Csaba Mihály
Lóránt Csaba Mihály

Reputation: 97

How to address changing, generated file name to variable?

I've 2 excel files. One is set to read only and collects data from database. The other excel file purpose is to copy the required columns. The code is up and running I've no problems with that. However, the generated excel file name always changes at the beginning of every month. To make the code working I need to change the source name inside the code every time. Anyone has any idea how to fix this problem?

Here is how I declare the workbook and worksheet variables:

'set variables for wkb and ws to copy
Dim wbSource As Workbook
Set wbSource = Workbooks("UK2_2016_06.xlsx")

Dim wsCopy As Worksheet
Set wsCopy = wbSource.Worksheets("UK2_2016_06") 'change name as needed

Upvotes: 1

Views: 36

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Is this what you want?

Dim mnth As Long, Dt As Long

mnth = Month(Date)
Dt = Day(Date)

'~~> If 06 is month then
Set wsCopy = wbSource.Worksheets("UK2_" & Year(Date) & "_" & Format(mnth, "00"))

'~~> If 06 is date then
Set wsCopy = wbSource.Worksheets("UK2_" & Year(Date) & "_" & Format(Dt, "00"))

Similarly for the workbook :)

Upvotes: 2

Related Questions