Solved Macro, merging two files alternating rows

December 10, 2012 at 01:58:37
Specs: Windows 7

Hi, I need a macro that will merge two populated files alternating row for row. eg: Lets say we are merging Sheet1 and Sheet2, whereby row1 of Sheet2 becomes row2 of Sheet1, row2 of Sheet2 becomes row4 of Sheet1, etc until finished. Each sheet contains hundreds of rows so it would need to be 'as long as' or similar. I would really appreciate hearing from you.

See More: Macro, merging two files alternating rows

Report •


#1
December 10, 2012 at 03:07:33
✔ Best Answer

Hi Igdent

To write a useful macro we would need more information about the specifics of your sheet formats and whether when you say "Files" your really mean sheets in the same file. Here is a simple macro that will give you some notion of what you would need...

Sub shuffle()
Application.ScreenUpdating = False

Sheets("Sheet1").Activate
    S1_LastRow = Range("A64000").End(xlUp).Row
        S2_LastRow = Sheets("Sheet2").Range("A64000").End(xlUp).Row
            S1_LastCol = Range("A1").End(xlToRight).Column
                S2_LastCol = Sheets("Sheet2").Range("A1").End(xlToRight).Column


Cntr = 1
For i = 1 To S2_LastRow Step 2
    Range(Sheets("Sheet1").Cells(Cntr, 1), Sheets("Sheet1").Cells(Cntr, S1_LastCol)).Copy
    Sheets("Sheet3").Cells(i, 1).PasteSpecial
    Range(Sheets("Sheet2").Cells(Cntr, 1), Sheets("Sheet2").Cells(Cntr, S2_LastCol)).Copy
    Sheets("Sheet3").Cells(i + 1, 1).PasteSpecial
    Cntr = Cntr + 1
Next


End Sub


Report •

#2
December 10, 2012 at 05:00:59

Fantastic, thank you. I will read through this in the morning (midnight here right now). Yes, I started out with files, then decided to copy one file into the next sheet of the previous file because I only wanted half of it. Long story. Hopefully, I will speak to you again tomorrow after I have rested my mind for a few hours.

Report •

#3
December 10, 2012 at 09:31:27

AlteK,

Why did you use this syntax to determine the last row in the sheets?

S1_LastRow = Range("A64000").End(xlUp).Row

With more rows available in Excel versions from 2007 and later, this syntax is the preferred syntax and is backward compatible with all versions:

S1_LastRow = Range("A" & Rows.Count).End(xlUp).Row

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
December 10, 2012 at 13:16:08

Worked magnificently, AlteK. Thank you so very much. It was way beyond my low level of expertise; and as tired as I was I did not think to paste the result into Sheet3. Such a brilliantly simple solution. Instead I was trying to squeeze Sheet2 into Sheet1. And thank you DerbyDad03 for your contribution; they both work beautifully as there were only 432 rows on each sheet.

Report •

#5
December 10, 2012 at 13:26:57

@igdent: glad (and shocked) that it helped

@DerbyDad03: the reason is simple. I am self taught at VBA and as a result will very often not use the most efficient code. My objectives here are to;

   help people where I can
   get more VBA practice
   learn new stuff from more experienced users

So, thanks for helping fulfil objective #3. Any feedback is always welcome. If you or anyone takes the time to do that there is no need to be gentle or polite about it - just tell me what I did wrong and how to improve it.


Report •


Ask Question