Copy/Paste Cells To/From Every Other Row

**kethryvalis** · 08-01-2012, 02:08 PM

I have a worksheet that is 2201 rows long (with header row), and every other row is a duplicate of the one above it. For every duplicate set, I need to copy column C in the first row and paste it into column B in the second row. For example (first column is B and second column is C):

01/01/2012 12/31/2012
01/01/2012 12/31/2012
01/01/2013 12/31/2013
01/01/2013 12/31/2013

The above needs to look like this when I'm done:

01/01/2012 12/31/2012
12/31/2012 12/31/2012
01/01/2013 12/31/2013
12/31/2013 12/31/2013

Does anyone know of an easy way to do this? I have tried to do this by hand, but it's going to take hours. I've attached a workbook (Copy&PasteExample.xlsx) with a sample. (Note: Column C in the workbook will be deleted after all of the dates are moved, so formulas cannot rely on that column's data remaining.)

I am working in Excel 2010 and I am a beginner at working with macros.

**arlu1201** · 08-01-2012, 02:40 PM

Does column B in the 2nd row already have data in it which needs to be replaced with data from column C?

**kethryvalis** · 08-01-2012, 02:45 PM

Yep, it does.

Also, I just realized something silly. It doesn't matter if the data in Row C is copied from the first or second row of the duplicate set since they're the same. So, if it's easier to copy/paste from the same row instead of two different rows, that is fine.

**arlu1201** · 08-01-2012, 02:47 PM

So you mean that data in row 3 (considering that rows 2 & 3 are containing duplicates) column C can be copied over to column B?

**kethryvalis** · 08-01-2012, 02:56 PM

Sorry, I'm being confusing. For example:

01/01/2012 12/31/2012
01/01/2012 12/31/2012

Pretend the two columns above are Rows 1 and 2, Columns B and C. I mean that since they're duplicates, the date can be copied from either C1 or C2 to B2.

After playing with my spreadsheet, it actually looks like this now:

01/01/2012
01/01/2012 12/31/2012

So, I really just need something that can easily copy C2 to B2, and perform that function for every other row. Would it help if I posted a new worksheet? (Sorry to be so confusing!)

**arlu1201** · 08-01-2012, 03:03 PM

I am fine

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**kethryvalis** · 08-01-2012, 03:03 PM

Actually, I may have figured out a weird workaround.

In a blank column off to the right, I entered the following:

=B1
=C2

Then, I highlighted those two cells and used the fill handle to copy that formula set all the way down the spreadsheet. That populated the dates I needed. Then, I just copied that column and pasted it over column B. I'm sure there's a fancier way to do it, but this works for me. Sorry for all the hubbub!

Thanks for the help, arlu1201!

**arlu1201** · 08-01-2012, 03:07 PM

Hey np.

The code was easy to do as well...just a couple of mins

---------- Post added at 12:37 AM ---------- Previous post was at 12:36 AM ----------

If you are satisfied with the solution(s) provided and your own as well, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save