Macro: copy and paste tabs

Resolved · High Priority · Version 2003

Tue 15 Nov 2011, 15:30 Delegate Heather said...

Heather has attended:
Excel VBA Intro Intermediate course

Macro: copy and paste tabs

I use a macro created by an ex-colleague which copied and pasted two tabs from a workbook into a new book.

It was done by recording a macro and in the past has copied and pasted both worksheets as values (both have many formulae). However i have made some adjustments to some formulae and now only these paste as "=N/A" when i run the macro. I have stepped through the macro and it is very early on where the "N/A"s appear.

The coding is as follows:

Sheets(Array("INPUT", "JOURNALS")).Select
Sheets("JOURNALS").Activate
Sheets(Array("INPUT", "JOURNALS")).Copy
Sheets(Array("INPUT", "JOURNALS")).Select

Is there any reason an adjusted formula would not copy accross? THe formula is as follows:

=IF(K26<>0,(VLOOKUP(K26,'CALL AC'!Print_Area,2,FALSE))," ")

Thanks,

Heather

Tue 15 Nov 2011, 16:09 Delegate Heather said...

RE: Macro: copy and paste tabs

I may have worked this our or at least found a if not the ideal solution. The vlookup was looking up values in a seperate tab. I then copied the table the formula was looking up into the current tab which i was copying and pasting, and it worked without N/As i then extended the macro to delete the table once the tabs had been copied.

Do copied and pasted as new book tabs not lookup in other tabs?

Thanks,

Heather

Fri 16 Nov 2012, 13:39 Trainer Gary said...

RE: Macro: copy and paste tabs

Hi Heather

Thanks for getting in touch. I believe the VLOOKUP is generating the #N/A because the FALSE argumented is selected. This means any item not found will be returned #N/A.

It is difficult to see without seeing the source data, but my guess is when the data was pasted the references were not updated, so the formula did not know to look in the other book where the table resides.

I hope this helps, if you have any further questions please let us know.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector