Macro to export to existing report

[gerhardl]

Hi there,

I do a report every day in excel from QlikView data. The excel file has 3 sheets.

Sheet1: Yesterday's data (sheet name is the date)

Sheet2: Today's data (sheet name is the date)

Sheet4: Differences for the day.

Sheet 2 shows the current data like number of accounts, turnover, etc.

Sheet3 then subtracts all the values in Sheet1 from the valaues in Sheet2 - so it shows the movement for the day (e.g. number of new accounts).

So every morning I copy the entire Sheet2 and paste it in Sheet1 (so sheet 3 will then have zero values everywhere). Then I update Sheet2 with today's latest data, and sheet 3 will automatically update and again show the differences. I rename the sheets and save the file with the latest date.

I have now created a macro (my first ever!), which generates the entire Sheet2 (today's data) in a new excel file.

My end goal is to do the entire report.

So instead of creating a new excel file, I want the macro to do the following:

1. Look for the LATEST file in my reports folder and open it.

2. Copy everything in Sheet2 to Sheet1 (so the 2 sheets are identical).

3. Export the data (as per my current macro) and paste it in Sheet2.

4. Save the file with the new date.

I know this is a long question - any help will be appreciated. I can post my current macro if needed.

My first obsticale is to open an exisiting excel file and copy and paste between sheets.

Thanks,

Gerhard

[m_woolf]

I may have been wrong when I said you needed to save the workbook before reloading.

That said, it is not obvious to me that you syntax is wrong.

The only thing that I see is a missing space bewteen the last & and ".xlsx", and I'm not sure that would cause a syntax error.

What error are you getting?

[gerhardl]

Figure it out - I was just being stupid:

Forgot to add this to the section where I try to open the file:

dtmDate = Date

strYear = year(Date)

strMonth = month(Date)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-1)

if len(strDay) = 1 then strDay = "0" & strDay

Thanks Again!