Excel macro-- copy cell to same column in next row

[spmartin2012]

I'm sure this is very simple, but I'm just starting with these types of tasks. I need to copy a cell value to the same column in the next row and cycle thru all data. I can use this twice to do this for 2 different columns or combine these two steps into one. More specifically: I need to copy cell A1 to B1 (etc.) and copy cell D1 to D2 (etc.)--all on the same worksheet. These 2 copy/paste operations need to continue until there is no data--which will happend at the same time for both sets. Any help is gresatly appreciated--I want to make this task faster and less boring!

[twinnyfo]

spmartin2012,

Although this is an Access VBA forum, please describe your data set. I'm not sure I follow what needs to happen, as the A1 to B1 copies data one cell to the right, and D1 to D2 copies data one cell down. I'm not sure I understand how to continue that until there is no data.....

Please explain, providing a sample of your data.

[spmartin2012]

Sorry for the typo--I want to copy both cells downward, starting with A1 and D1 and repeating until there is no data. I have already used a macro to insert a blank line below each original row, so there are blank rows in which to paste the copy of the cell value. Columns A and D will always have the same value in the newly created row as the row above. The other columns are for manually entered data. THANKS so much for any help!

[Rabbit]

What code do you have so far?

[spmartin2012]

I'm playing around with it:

Dim h As Long, r2 As Range
h = 1   'InputBox("type the number of rows to be insered")
 
Set r2 = Range("A1")
Do
Range(r2.Copy, A2, Offset(1, 0)).PasteSpecial , xlPasteSpecialOperationNone
 
Set r2 = Cells(r2.Cells.Value + h + 1, 1)
 
If r2.Offset(1, 0) = "" Then Exit Do
 
Loop

[spmartin2012]

Another typo, it is:

Dim h As Long, r2 As Range
h = 1  
 
Set r2 = Range("A1")
Do
Range(r2.Copy, A2, Offset(1, 0)).PasteSpecial , xlPasteSpecialOperationNone
 
Set r2 = Cells(r2.Cells.Value + h + 1, 1)
 
If r2.Offset(1, 0) = "" Then Exit Do
 
Loop

[Rabbit]

And what problem are you having with the code? Are you getting an error?

[spmartin2012]

I get an error of "Sub or Function not defined", referring to the word "Offset" on line 6. THANKS FOR ANY HELP!

[Rabbit]

Offset is a method of the range class. You can't use it by itself, it must reference a range object.
So you must use

Range("A1").Offset(...)

By itself, it is not defined.

Offset(...)

But I believe the point is moot. You're overcomplicating it, you can accomplish what you want in a single line of code.

Range("A1:A3").Copy Range("B1")

[zmbd]

here's from a code I use that enters some 100+ formulas:

'move to the left one cell and continue
ActiveCell.Offset(0, 1).Range("a1").Select

[spmartin2012]

Actually, what I want to do is copy cell A2 to A3; A4 to A5, A6 to A7, etc until there are no more records. And I also want to do the same for column D: copy D2 to D3, D4 to D5, D6 to D7, etc. I was trying to use the Range to have the code progress down column A, doing the copy/paste. Thanks very much for your help. Sorry--I guess I didn't explain it well.

[Rabbit]

You can do that with minor modifications to the code I posted.