How do I auto increment row number in pasting macro? (Code inside)

**paulishuku** · 02-09-2011, 07:59 PM

Hey everyone,

I trying to create a macro where I have a specific amount of rows in a column copied and I want to paste it. But every time I paste it I want to increase the row number say A32 to the next one up. I have to go all the way to A2000 so it would be a lot of copying and pasting. Here's the macro I got going on now.

Please Login or Register  to view this content.

The reason for the A848 is because that's just the generic column I already have copied. I want to add 1 to the A32 every time I paste it. Is this possible?

**davesexcel** · 02-09-2011, 08:23 PM

Hello,
I am not sure what you are asking as your code shown is find-replace, not copy-paste as you have indicated

**MarvinP** · 02-09-2011, 08:38 PM

Hi paulishuku,

You need to learn a little code. To find the last row with data in it in Column A you need stuff like

Please Login or Register  to view this content.

So when you copy stuff and then want to paste it at the bottom you get the location of the bottom by adding 1 to the LastRow Above.

Search this site for "LastRow" as there are many example explaining how it works.

hope this helps.

**paulishuku** · 02-09-2011, 08:40 PM

Hey Dave,

I'm probably going about what I'm doing the hard way but this is the code I have for 2000 rows:

Please Login or Register  to view this content.

Sheet 1 is a huge list of imported data from an activity log I do once a week. The column Q in that huge list of activity logs is hours worked. The column L in sheet 1 is an asset ID. In sheet 2 is a list of all the asset IDs our department has, theres around 1000 of them. So what this code is doing is trying to find a specific asset ID in a row and if its there displaying the hours worked on it, and if not displaying 0. I have this code copied 2000 times with ascending rows in columns Q and L. What I want to do is do this for each asset ID. Which is where the A53 comes in to play. The reason I'm using the replace function is because I've just been been pasting a column with

Please Login or Register  to view this content.

in the rows and replacing A848 with whatever A row im actually up to.

So what I'm essentially asking is if there is a way I can, instead of copying and replacing the Arow if there is a macro that will automatically do it for me 1000 times.

I've tried autofill and I cant get to increment the A#.

Any ideas on what I can do?