VBA code to copy and paste multiple times

[MrsFraser07]

Hi there, I get an awful government report every month that I want to have a macro that will automatically copy and paste multiple times. The file looks like this when I get it:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]License Number
[/TD]
[TD]License Status
[/TD]
[TD]UWI
[/TD]
[TD]Deemed Asset Value
[/TD]
[TD]Deemed Liability Amount
[/TD]
[TD]PVS Value
[/TD]
[/TR]
[TR]
[TD]25393
[/TD]
[TD]Suspension
[/TD]
[TD]12-28-081-09W5
[/TD]
[TD][/TD]
[TD]83956
[/TD]
[TD]Inactive
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type
[/TD]
[TD]Liability
[/TD]
[TD]Included in Cost
[/TD]
[TD]Deemed Liability Amount
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]WB Abandonment
[/TD]
[TD]Y
[/TD]
[TD]60081
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Reclamation
[/TD]
[TD]Site Reclamation
[/TD]
[TD]Y
[/TD]
[TD]23875
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28928
[/TD]
[TD]Issued
[/TD]
[TD]04-16-085-09W5
[/TD]
[TD][/TD]
[TD]92614
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Type
[/TD]
[TD]Liability
[/TD]
[TD]Included in Cost
[/TD]
[TD]Deemed Liability Amount
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]Additional Event
[/TD]
[TD]Y
[/TD]
[TD]13748
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Abandonment
[/TD]
[TD]WB Abandonment
[/TD]
[TD]Y
[/TD]
[TD]54991
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Reclamation
[/TD]
[TD]Site Reclamation
[/TD]
[TD]Y
[/TD]
[TD]23875
[/TD]
[/TR]
</tbody>[/TABLE]

What I would like my macro to do, is delete the blank rows (there are always 2 between each data set per license number), then copy the license number, then paste it in all the blank cells underneath it until it reaches the next unique license number. Then copy the new license number, paste into all blank rows beneath it, until it reaches the next, and so on. Hopefully that make sense? Any help would be most appreciated! My report currently has over 25,000 rows and I would hate to have to copy and paste them!

Thanks!
Shari

 

[MrsFraser07]

When I run the script from post #4 , it does fill in all the license numbers, but doesn't delete the rows. I get an error at Columns(1).SpecialCells(xlBlanks).EntireRow.Delete The error I get is Run-time error "1004': Method 'Range' of object'_Global' failed

To be honest, I really don't need those extra rows deleted, I just though it would be cleaner. The main thing was to populate the license number multiple times. I really appreciate your help! I'm good with formulas in excel, just trying to build my skills and get into some VBA!!!

 

[MrsFraser07]

When I run the script from post #4 , it does fill in all the license numbers, but doesn't delete the rows. I get an error at Columns(1).SpecialCells(xlBlanks).EntireRow.Delete The error I get is Run-time error "1004': Method 'Range' of object'_Global' failed

To be honest, I really don't need those extra rows deleted, I just though it would be cleaner. The main thing was to populate the license number multiple times. I really appreciate your help! I'm good with formulas in excel, just trying to build my skills and get into some VBA!!!

 

[MrsFraser07]

Sorry for all the double posts! My computer (or this site) is super slow today and for some crazy reason my posts are being posted twice!!!

 

[Fluff]

Sorry for all the double posts! My computer (or this site) is super slow today and for some crazy reason my posts are being posted twice!!!

I think that there are ongoing problems with the site, since an upgrade as few weeks ago. If you are getting a pop-up asking if you want to leave the page, I've discovered that if you say no then the post will appear twice.

I get an error at Columns(1).SpecialCells(xlBlanks).EntireRow.Delete The error I get is Run-time error "1004': Method 'Range' of object'_Global' failed

I'm not quite sure why you're getting that error, but an alternative would be to remove it & the do a sort on column A

 

[MrsFraser07]

Thanks for all your help Fluff!!! I took out that last line of code and it works beautifully now! All my license numbers get populated. The blank rows are not deleted, but I really don't care. I can always do a quick "Go To Special" and highlight all my blank rows and get rid of them that way. No issues if they stay in my sheet though. Again, I very much appreciate your help!!! You saved me from thousands of copy and paste operations!

 

[Fluff]

Glad to help & thanks for the feedback