Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Copy loop in Excel

Sep 10, 2005 5:06AM PDT

Hi, I hope someone can help me. I would like to copy multiple things in excel and paste them into another sheet. Example, Copy A1 from sheet 1 into sheet 2 and paste to b3 then copy d1 from sheet 1 into sheet 2 and paste it to b5. I then I want to move down to the next row on sheet one and start the process all over but on sheet 2, it would be b7 and then b9. Any way this can be put into a macro?

Discussion is locked

- Collapse -
Yes it can
Sep 10, 2005 6:24AM PDT

Go to Tools > Macro > Record New Macro. Now just go through the copies just as uou said. If you have only two or three lines to do, just continue and stop the macro when finished.

If you have many lines, then put the recorded macro inside a loob covering the lines of interest. If the ending line is not always the same, use a dialog box to tell the macro when to stop.

Hope this helps.

Frank

- Collapse -
How would the loop read?
Sep 10, 2005 9:19AM PDT

Would the loop be an If then else statement? I'm not sure.

- Collapse -
Re: copy loop
Sep 10, 2005 7:55PM PDT

JSteinmetz,

I'm afraid Franks solution is somewhat too simple.

The classic loop statement in Basic is a FOR ... NEXT construction, like in for myrow = startrow to endrow ... next mtrow. That seems quite possible, if you use Selection.Rows.Count to determine the number of rows.
But a DO ... LOOP UNTIL ... is possible also. It's all in the general VBA help files.

If you take A1 as a reference on both the source and the target worksheet, you can loop through the rows and select the columns through the offset method of that cell. From your description, it seems the row offset should vary by 1 in each loop in the source -
Range("A1").Offset (myrow,mycol) - and by 4 in the target spreadsheet - Range("A1").Offset (4*myrow-1, mycol). If I read it correctly, row 1 goes to row 3, row 2 to row 7, so it moves four times as fast.

This is all in the VBA Help files for Excel.

Hope this helps.


Kees

- Collapse -
wonderful
Sep 10, 2005 10:26PM PDT

I will read the help file and see what it says so I could figure this out. Thank you!!

- Collapse -
Just a warning.
Sep 11, 2005 1:01AM PDT

Programming a macro like this without programming experience, without reading though a tutorial or a book written for beginners on programming VBA in Excel, is DIFFICULT. The help files, more or less, are meant for people who know the things in principle, just need to check on the details. I don't think they are suitable for starting programmers.

Anyway, you've got something to do.

Kees

- Collapse -
ok, this is what I came up with
Sep 11, 2005 6:32AM PDT

Sub testcopy()
'
' testcopy Macro
' Macro recorded 9/10/2005 by me

'
For a = 1 To 10

Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C3").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C6").Select
ActiveSheet.Paste

Next

End Sub


As a test, I made the For statement from 1 to 10 to test it. I haven't figured out though how to get the cells to incliment though, ex A1 to A2, A3, etc and like all the others need in incriment depending on where the text is and where it needs to get pasted to.

- Collapse -
Re: what you made
Sep 11, 2005 7:33AM PDT

Yes, that's the easy part, the recording.
Now for the DIFFICULT part, the programming, as I outlined a few posts above. Quite something else!

Let me guess: our (ex-?)member swisse should have done in 15 minutes. And let me guess again: it will take you 2 days. No offence intended, of course, just a guess. And I really hope I'm wrong.

Kees

- Collapse -
More then that
Sep 11, 2005 8:46AM PDT

It will probably take me more than two days to do what I need. lol With work and everything else going on in a day. You know how it is.