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