Macros in Excel 2003
I am trying to create a macro that will collect information from particular cells in an Ecel spreadsheet (A) and copy and paste them into another spreadsheet (B).
I can do this no problem, but the issue is that I want to run this same macro on a number of different spreadsheets, so that all the information get pasted into spreadsheet B.
How can I do this so that the information selects the next available column or row to paste to, rather than always pasting into the same row, therefore replacing data or throwing out an error message?
Have a look at this code
r = 0
n = 0
For i = 1 To 3
n = n + r
r = Selection.Rows.Count
Cells(1, 1).Offset(n, 0).Select
It merges all data from the first 3 worksheets in to the 4th worksheet
I'm sure you'll work out how to adapt it to meet your requirements
Hope it helps