Automatically Skip Lines in Excel? 1

[jt463]

The following is a macro I recorded:

Range("A1").Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Range("E2").Select
ActiveSheet.Paste

and so forth...

What I want to do is to create a loop where the VB will simply move to the next record on the spreadsheet (3 rows below the first copy range) and then follow the same procedure, pasting that information into the row below the row that just was just pasted.

In other words, my example is:

(Original Record)
A
1 John Doe
2 1234 Anystreet
3 Anyville, ST 12345
4 Jane Doe <----New Record Start
5 4321 Anystreet
6 Anyville, ST 54321

(Goal Record)
AA BB
1 John Doe 1234 Anystreet...

So, I would need the macro to do the copy/paste from A1 to AA1, then A2 to BB1, and so forth. Then I would need the VB to tell it that when it finishes copying from A3 to CC1 that it needs to copy A4 to "AA2"...

Then somehow loop it so that when it finishes A6 that it knows that A7 is a new record and that needs to go to AA3.

This is the only way I can think of accomplishing this so that the data can be used in a mail merge.

Any suggestions would be greatly appreciated!

Thank you.

 

[Zathras]

You don't need VBA. Put these formulas in the cells indicated and copy down:
[tt]
B1: =IF(MOD(ROW(),3)=1,A1,"")
C1: =IF(MOD(ROW(),3)=1,A2,"")
D1: =IF(MOD(ROW(),3)=1,A3,"")
[/tt]
Then copy and paste special / values.
Delete column A.
Sort descending and then remove the blank lines.

If you want to automate, use the macro recorder.

 

[jt463]

Thanks for taking the time to respond Zathras.

I was unfamiliar with the =IF(MOD... option.

I tried your suggestion and simply entered the formulas into B1, C1, and D1. Everything was great and provided the result similar to if I would have paste special / transpose.

However, when I copied down, the B1 formula to B2, it automatically modified the formula to be =IF(MOD(ROW(),3)=1,A2,""). Then C2 would pull from A5, and so forth.

What I want to achieve is that in B2, the formula would pull the data from A4 - since that is the beginning of the next record.

The copy-down function seemed to just assume that since B1 referenced A1 that B2 must be looking at A2. However, I want B2 to look at A4...

Does that make sense?

 

[jt463]

Hold that thought. I actually followed your steps to the end this time where before I didn't see the results I was looking for at the beginning, so I just stopped...I will let you know.

 

[Zathras]

B1 looks at A1, MOD(ROW(),3) = 1
B2 looks at A2, MOD(ROW(),3) = 2
B3 looks at A3, MOD(ROW(),3) = 0
B4 looks at A4, MOD(ROW(),3) = 1
etc.

Because MOD(ROW(),3) is not 1 on row 2, the IF tests false and the ELSE value of "" is taken.

Similarly on row 3 the formula gives a blank
However on row 4, the formula takes the value from column A
etc.

Please try again and follow my instructions exactly.

 

[jt463]

Zathras, you are brilliant!

It worked like a charm. Kudos.