Need help to write a macro to do copy and paste in a number of worksheets
Posted by Pankaja Narayanan on February 09, 2001 5:28 AM
I need help to write a macro. My workbook contains 19 worksheets and there is data in column BY computed using VLOOKUP in each sheet. How can I write a macro that can copy the contents of column BY (Rows 5 to 450) and paste to column AS as values simultaneously in (1) the same workbook and (2)in a different workbook. Thanks.
Posted by Celia on February 09, 2001 9:28 PM
Do you need to do this once only? If so, it would be quicker to do it manually than to write a macro.
If you need to do it frequently, sounds like you could produce a macro to do what is required with the macro recorder.
Celia
Posted by Pankaja Narayanan on February 10, 2001 5:44 AM
Re: Celia, need help for a copy and paste macro
Thanks Celia for responding. I need to do this periodically in all the 19 worksheets in the same workbook as well as a different workbook. The macro works for the active worksheet and I have to run the macro 19 times making each of the 19 sheets active sheets. Could you tell me how I can specify the different worksheet names in the nacro? Thanks.
Pankaja
Posted by Celia on February 10, 2001 7:35 AM
Re: Celia, need help for a copy and paste macro
Pankaja
You shouldn't have to copy/paste 19 times nor run the macro 19 times.
You didn't say whether you have to copy column BY from one sheet to column AS on all sheets or copy column BY on each sheet to column AS on each sheet.
In any event, the principle is the same.
If you need to copy column BY from one sheet only:- Select the column, copy, select all sheets in the workbook, select column AS, paste special values.
Do the same to paste to another workbook. If you have the macro recorder on while you do it, you will have your macro.
If you need to copy column BY on each sheet to column AS on each sheet, :- Select all sheets in the workbook, select column BY, copy, select column AS, paste special values.
Celia
Posted by Pankaja Narayanan on February 10, 2001 8:23 AM
Re: Thanks Celia very much for your help. Please clarify a bit more.
Celia,
Thank you very much. The column BY values are different for the 19 sheets. I need to copy column BY from each of the 19 sheets and paste as values in column AS in the corresponding sheet.
How would that work if I highlight all the 19 sheets.
How would that work if I have to copy column BY from each of the 19 sheets to column AS in each of the 19 sheets in a different workbook?
Posted by Celia on February 10, 2001 8:32 AM
Re: Thanks Celia very much for your help. Please clarify a bit more.
Follow the last sentence of my previous post :-
Select all sheets in the workbook, select column BY, copy, select column AS, paste special values.
Select all sheets in the workbook, select column BY, copy, select column AS, paste special values.
Works the same way :-
Select all sheets in workbook 1, select column BY, copy, select workbook 2, select all sheets, select column AS, paste special values.
Celia
Posted by Celia on February 10, 2001 8:35 AM
One more point
Make sure the sheets in both workbooks are in the sam sequence.
Posted by Pankaja Narayanan on February 10, 2001 11:42 AM
Re: Thanks once again, Celia. IT WORKED!!!!
Celia,
Thanks. IT WORKED VERY WELL!
