Macro issue - need the code to apply to unique workbook rather than just book1

[Chriz]

Hi All,

I am a novice when it comes to macros, but I know how powerful a tool they can be! So I have tried to start recording my own to try and reduce my workload. I've been trying to put a macro together for a daily report that's created. Basically, on the new workbook created daily I'd firstly like to input in cell X1 a header 'Gift Catalogue?' Secondly I'd like to Copy and paste Columms A:X to a new workbook. After that's done in X2 I'd like to do a VLOOKUP from an existing spreadsheet that's on the server, and then paste the vlookup formula down to say X1000 or so. Here's where I've got so far with the code, it's not working quite as I'd like at the moment the issue being when I open the daily report it will always have a unique filename, which is where I think I've got the problem as I've got this to work when the file name stays static, but when I opened a different report it stopped working? I've done a bit of research but as I'm such a newbie I'm not quite sure where to go - I have a feeling I may need to declare a variable so the active workbook is switched back to the original workbook when it runs the vlookup? Anyways hopefully this is a pretty easy fix? Here's the code I've got so far:

Please Login or Register  to view this content.

Thanks in advance.

Chriz

[Norie]

Chriz

Where are you adding/creating the new workbook?

In the code where are you opening the other workbook?

How will that workbook's name change?

Would it be acceptable to prompt the user to select the workbook via a dialog?

[Chriz]

Hi Norie,

I'm saving the new excel spreadsheet to the same folder (I've had to replace the file location on the above as it includes my business name) but with an amended file name of something like 16.02.13Amended.

The section of code that opens the always static in location workbook that I want to run the vlookup from is

Please Login or Register  to view this content.

I don't want to alter the above file, I just want to use the information contained within it to run the vlookup in X2 and downwards in the spreadsheet that copies columns A to X if that makes sense.

So basically steps as follows:

I open the daily report file.

In Cell X1 of the daily report file, I enter the Header 'Gift Catalogue?'

I then copy the columns A to X onto a new spreadsheet with no designated filename as yet.

I then open the spreadsheet I need to run the Vlookup info off:

Please Login or Register  to view this content.

In the newly created spreadsheet that has the pasted columns A to X I run a vlookup in X2 that is then pasted downwards to around X1000.

Hope that clarifies things.

Thanks

[Norie]

So which file is it that has a unique filename?

Where are you copying A to X from?

[Chriz]

Hi Norie,

Thanks for your time, I have figured it out now, I ended up using the following code:

Please Login or Register  to view this content.

I'll test it at work on Monday as don't have access at home but seems to work ok here.

[Norie]

You could probably tidy that up a bit.

Please Login or Register  to view this content.

[Chriz]

Many thanks!! Yes, thanks for tidying it up. Will set this as solved.