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.
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
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.
AlteK, Why did you use this syntax to determine the last row in the sheets?
S1_LastRow = Range("A64000").End(xlUp).RowWith 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.
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.
@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 usersSo, 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.
