Hi
I have sheets (Data1, Data2, Data3, Data4, Data5) populated via links to another spreadsheet.
Data1, Data2, Data3, Data4 & Data5 are all same format, width range A1 to HY and each sheet goes down to a different row number.
I need a macro to do the following process;
1. I want to set the row number to go to for each sheet (Data1, Data2, Data3, Data4 & Data5).
2. Select range within 1st sheet Data1 A1 to HY1500 (set the row in macro), Copy, go to blank sheet “Data” then Paste Values Only (eg. Paste Special>Values)
3. Then, Goto next sheet “Data2”, select range A3 to HY500 (set the row in macro), Copy, go to sheet “Data” end of previous pasted data A1501 (ie. from row set in macro), then Paste Values Only (eg. Paste Special>Values)...
4. Then, Goto next sheet “Data3”, select range A3 to HY300 (set the row in macro), Copy, go to sheet “Data” end of previous pasted data now A2001 (ie. from last pasted data), then Paste Values Only (eg. Paste Special>Values)...
5. Then repeat the same for Data4 and Data5.
I will end with “Data” containing the pasted values from Data1, Data2,....to Data5.
Many thanks
Rob