Macros in Excel 2003

[LeonieCol]

Hi,

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?

[The_Original_Invisible]

Have a look at this code

Sub ConsolLoop()
Sheets(4).Select
Cells.ClearContents
r = 0
n = 0

For i = 1 To 3
Sheets(i).Select
GoSub DoCopy
GoSub DoPaste
n = n + r
Next i

Exit Sub

DoCopy:
Cells(1, 1).CurrentRegion.Select
Selection.Copy
r = Selection.Rows.Count
Return

DoPaste:
Sheets(4).Select
Cells(1, 1).Offset(n, 0).Select
ActiveSheet.Paste
Return

End Sub

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

Lee