469,127 Members | 1,327 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

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

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!
Oct 5 '12 #1
11 5270
twinnyfo
3,653 Expert Mod 2GB
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.
Oct 5 '12 #2
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!
Oct 5 '12 #3
Rabbit
12,516 Expert Mod 8TB
What code do you have so far?
Oct 5 '12 #4
I'm playing around with it:

Expand|Select|Wrap|Line Numbers
  1. Dim h As Long, r2 As Range
  2. h = 1   'InputBox("type the number of rows to be insered")
  3.  
  4. Set r2 = Range("A1")
  5. Do
  6. Range(r2.Copy, A2, Offset(1, 0)).PasteSpecial , xlPasteSpecialOperationNone
  7.  
  8. Set r2 = Cells(r2.Cells.Value + h + 1, 1)
  9.  
  10. If r2.Offset(1, 0) = "" Then Exit Do
  11.  
  12. Loop
Oct 5 '12 #5
Another typo, it is:

Expand|Select|Wrap|Line Numbers
  1. Dim h As Long, r2 As Range
  2. h = 1  
  3.  
  4. Set r2 = Range("A1")
  5. Do
  6. Range(r2.Copy, A2, Offset(1, 0)).PasteSpecial , xlPasteSpecialOperationNone
  7.  
  8. Set r2 = Cells(r2.Cells.Value + h + 1, 1)
  9.  
  10. If r2.Offset(1, 0) = "" Then Exit Do
  11.  
  12. Loop
Oct 5 '12 #6
Rabbit
12,516 Expert Mod 8TB
And what problem are you having with the code? Are you getting an error?
Oct 5 '12 #7
I get an error of "Sub or Function not defined", referring to the word "Offset" on line 6. THANKS FOR ANY HELP!
Oct 8 '12 #8
Rabbit
12,516 Expert Mod 8TB
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
Expand|Select|Wrap|Line Numbers
  1. Range("A1").Offset(...)
By itself, it is not defined.
Expand|Select|Wrap|Line Numbers
  1. Offset(...)
But I believe the point is moot. You're overcomplicating it, you can accomplish what you want in a single line of code.
Expand|Select|Wrap|Line Numbers
  1. Range("A1:A3").Copy Range("B1")
Oct 8 '12 #9
zmbd
5,400 Expert Mod 4TB
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
Oct 8 '12 #10
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.
Oct 9 '12 #11
Rabbit
12,516 Expert Mod 8TB
You can do that with minor modifications to the code I posted.
Oct 9 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by DataMo | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.