Problem "save as" in a workbook with a macro

[excelsoil]

Hi all,

New to this forum

The macro i am trying to create is a form control button in a "Template worksheet" that copies and pastes a client list from a "Master list" to the "Template worksheet" to update the client list. The problem i am having is when the worksheet is saved as a different file name (it is needed) the macro doesn't recognize the "Move back to master file" command as I have the original file name there. Is there a way around this so that I can save this file as a different name and still get the macro to work?

This is the current macro i have:

Please Login or Register  to view this content.

[humdingaling]

OP Complied with request

[excelsoil]

All fixed sorry

[humdingaling]

is this part of some bigger code?
filetype and filepath is not declared

also what is
Workbooks("Agg-0001 -July15 V - Particle Density Coarse Aggregate - Do not use - new lookup").Activate
is this the workbook itself?

[excelsoil]

No this is the code that works when the button is pressed on the "Template worksheet" which on this template the file name is currently "Agg-0001 -July15 V - Particle Density Coarse Aggregate - Do not use - new lookup" when i save this file as a different name this doesn't work because it's trying to revert back to this file name not the new one

[humdingaling]

why not set variable for activeworkbook or thisworkbook?

[excelsoil]

Where would i place this into the code and how to word it. Sorry I'm kinda new at VBA.

[humdingaling]

depends on where you run the code
if the code is the master file itself then
this code should work

Please Login or Register  to view this content.

if the code is run when the workbook is active
set variable up earlier in piece

Please Login or Register  to view this content.

ps outputcol should be column?

also your copy range is A to N but your outputcol only increments 1...so some overwriting will happen
is that by design?

[excelsoil]

OMG you sir are awesome. You don't realize how much time you will save me. The first code worked perfectly. I just didn't know what to call it so i put the file name there.

[humdingaling]

just some extra optimisation

with this

Please Login or Register  to view this content.

if you only need one column you can do A1:A500
otherwise change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

also
this may also help streamline if you getting pop up message asking for confirmation

Please Login or Register  to view this content.

turn off before loop and turn on after