Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help make this Archiving Macro work...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help make this Archiving Macro work...
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Arlen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to make an excel macro to work with all sheets? | Excel Programming | |||
Can't make loop macro work - help? | Excel Programming | |||
how do I debug my Excel macro & make it actually WORK? | Excel Programming | |||
How do I make a macro work in one worksheet only | Excel Programming | |||
Macro to make a cell work like a check | Excel Programming |