Copying Data from Old Workbook to New Workbook

[NateNeedsVBAHelp]

I am trying to automate the process of filling out monthly reports and I have run into a problem I just cannot seem to work around.

These reports contain both weekly and monthly information, but because most months do not end on a friday or begin on a monday I have to have a couple of the week reports as partial weeks (e.g. April ended on a Thursday and so that week's report has both April 27-30 and May 1). When the first week of the next month's report runs I would like it to prompt the user to locate the last month's report, open the report, find week 5 of that report and copy the relevant data into the current month's report. (Hopefully that explanation is sufficient)

What I am running into is, after prompting for and opening the past month's report I am trying to select the Range A137:I232 on Sheet5 but instead the previous month's report just opens to wherever it was when it was last saved and won't select any ranges I tell it to, it just selects the range it was on last time it was saved. It will then copy that range and paste it into the Current Month's report.

below is my code, I apologize if it's not pretty, I'm still getting the hang of this.

Please Login or Register  to view this content.

It works wonderfully right up until it gets done opening the other workbook. no errors are thrown, it just doesn't activate the sheet or select the range as i've specified. I've tried a hundred different ways of trying to get it to do so (dimming workbooks, worksheets, ranges, etc.) and they all end exactly the same way.

Any help would be greatly appreciated.

Nate

[Greg M]

Hi there,

Well I think the main problem is the line:

Please Login or Register  to view this content.

This can be VERY dangerous if you don't fully realise what you're doing! Basically it tells Excel to ignore any errors it encounters and to just carry on regardless. I'm fairly certain that the workbook you selected for opening doesn't HAVE a worksheet with the tabname "Sheet5", so when Excel executes the line

Please Login or Register  to view this content.

it can't find the worksheet (and therefore can't activate it) but because of the "On Error Resume Next" command it happily copies and pastes the data from whatever worksheet happens to be already active in the active workbook (i.e. whatever worksheet was active when the workbook was last saved).

As far as I can tell, the "On Error Resume Next" isn't actually needed in your code, so delete it, run the code again and see if the "ActiveWorkbook.Worksheets("Sheet5").Activate" line generates a "Subscript out of range" error - this will happen if the opened workbook doesn't contain a worksheet with the tabname "Sheet5".

Hope this helps - please let me know how you get on.

Regards,

Greg M

[ravishankar]

Hi
How are your excel files of previous months named? NateMar2009.xls
Ravi

[NateNeedsVBAHelp]

That did it GregM, thanks for the response. I had the on error resume next in there because when the user presses cancel on the open dialogue box it would throw an error and I forgot that it would also be skipping the errors in the rest of the code as well.

The main problem was my not understanding the use of the Worksheets() operator. I had put "sheet5" in it because I figured that either that or the displayed name would work. I changed Activeworkbook.worksheets("sheet5").activate to ActiveWorkbook.Worksheets.("Week 5").Activate and it works like a charm.

On that note, what is a better way to deal with the error thrown when the user presses cancel on the Open File dialogue box?

[Greg M]

Hi again,

I'm using Excel 2000 and pressing Cancel or clicking on the X of the GetOpenFilename dialog box doesn't produce an error.

If you ever need to use the "On Error Resume Next" command you should re-enable Excel's error handling as soon as possible afterwards by using the command "On Error GoTo 0" (that's a zero), i.e.:

Please Login or Register  to view this content.

Hope this helps - please let me know if you need any further information.

Regards,

Greg M

[NateNeedsVBAHelp]

Thanks Greg, that's perfect. I really do appreciate the quickness of the response.

[Greg M]

Hi again,

Many thanks for your feedback - I'm glad I was able to help.

One point which I forgot to mention earlier - your statement:

Please Login or Register  to view this content.

actually causes LastMonth to be declared as a VARIANT. If you want both variables to be String types you should use:

Please Login or Register  to view this content.

Regards,

Greg M

[NateNeedsVBAHelp]

That is actually quite helpful. I just realized that there are a lot of places that my code is slowed down just a little because i'm using a lot more variants than I thought I was. Thank you for the clarification.

[Greg M]

Hi again,

Many thanks for your feedback - I'm very pleased that I was able to help.

Regards,

Greg M