Please help make this Archiving Macro work...

[Arlen]

I am trying to copy and paste multiple ranges of varying length from several
archives to one master sheet. This code almost does the job, except that
when each column is a different length, it only takes until the second block
of pasted data for the columns not to be in sync anymore.

Could someone please help me modify this? I'm trying, but lord help me and
VBA in the same room together.

With ThisWorkbook.Worksheets("2007")
Range("B4", Cells(Rows.Count, "B").End(xlUp)).Copy
..Cells(Rows.Count, "A").End(xlUp).Offset(1)
Range("C4", Cells(Rows.Count, "C").End(xlUp)).Copy
..Cells(Rows.Count, "B").End(xlUp).Offset(1)
Range("N4", Cells(Rows.Count, "N").End(xlUp)).Copy
..Cells(Rows.Count, "C").End(xlUp).Offset(1)
Range("O4", Cells(Rows.Count, "O").End(xlUp)).Copy
..Cells(Rows.Count, "D").End(xlUp).Offset(1)
Range("P4", Cells(Rows.Count, "P").End(xlUp)).Copy
..Cells(Rows.Count, "E").End(xlUp).Offset(1)
End With

I thank you heartily.

Arlen

 

[Don Guillett]

If we assume your source data is on different rows and you want it all to be
on the SAME row. NOT tested
With ThisWorkbook.Worksheets("2007")
dlr=.Cells(Rows.Count, "A").End(xlUp).row+1

Range("B4", .Cells(Rows.Count, "B").End(xlUp)).Copy .cells(dlr,"a")
Range("C4", .Cells(Rows.Count, "C").End(xlUp)).Copy .cells(dlr,"b")
etc

end with

..Cells(Rows.Count, "B").End(xlUp).Offset(1)
Range("N4", Cells(Rows.Count, "N").End(xlUp)).Copy
..Cells(Rows.Count, "C").End(xlUp).Offset(1)
Range("O4", Cells(Rows.Count, "O").End(xlUp)).Copy
..Cells(Rows.Count, "D").End(xlUp).Offset(1)
Range("P4", Cells(Rows.Count, "P").End(xlUp)).Copy
..Cells(Rows.Count, "E").End(xlUp).Offset(1)
End With

 

[Arlen]

Don,

Thanks for your help. I have 5 columns of data, each of different length,
with data in random row numbers.

Basically, this macro needs to copy and paste all this data as intact
(blanks preserved) into cols A:E of a new master sheet. If the longest data
column is 180 rows tall, then all 5 columns need to be treated as if they
were 180 rows tall. That way, the next sheet's data all gets pasted starting
at 181.

So, that's my dilemma. It's possible, right?

Thanks.

Arlen