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
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
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
