Solved: Copy & Insert Macro - Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Kophee

Thread Starter
Joined
Nov 12, 2011
Messages
5
Good morning. I consider myself a novice when it comes to Macros. I have written several simple macros using the macro recorder and VBA. My current dilemma is I have a template (worksheet #2) in which I wish to copy a range of rows several times within the same worksheet with a blank row between each copied range. The number of copies is dependent on the number of rows of data (cost centers)within worksheet #1.

I have seen some macro language that should accomplish the copy range, however, the next step is what throws me. For each copy of the template range, I'd like to insert the data (cost center) that comprises the rows on worksheet #1.

Essentially I want to create a copy of the template range for every cost center listed on worksheet #1. See the attached example.

Once the copies are made and the cost centers are inserted, my database will refresh the worksheet #2 and bring forth the data for each cost center. Macro language is not needed for the last step.

Thanks in advance for your assistance. Please let me know if the inquiry is not clear at any point.
 

Attachments

Joined
May 26, 2011
Messages
255
Kophee,

Welcome to the forum.
See if this attachment is what you want. It will look at sheet 1 for the center and then loop down. Sheet 2 will be updated for each center. You can rename the sheets to the actual sheets names.
The code works of the assumption sheet2 will already have The Months and the Column B info. It will enter the "Cost Center" and the cost center number. It will the copy column B.
 

Attachments

Kophee

Thread Starter
Joined
Nov 12, 2011
Messages
5
Thanks for the code, but I'm having trouble getting it to work. I made the changes to the appropriate sheet names, but the Macro gets hung. More than likely I'm forgetting something. The previous template was merely an example. Now that I'm at work, I can forward the actual file I'm working with.
 

Kophee

Thread Starter
Joined
Nov 12, 2011
Messages
5
Please Help!!!!! I couldn't get the original code suggested to work. The attached is a better example of what I must turn tomorrow by end of day. I don't want to manually create statement for approximately 1000 cost centers.

What I'm looking for is a macro that will make copies of the template as many times there appears to be cost centers on another worksheet. Essentially there should be a statement for each cost center. Advise if you have questions. Thanks.

Please see original post for more details.
 

Attachments

Joined
Sep 4, 2003
Messages
4,916
This code should do the trick. See attached example

Code:
Sub CostCenters()

For Each vCenter In Sheets("Cost Center List").Range("A4:A" & Sheets("Cost Center List").Cells(Rows.Count, "A").End(xlUp).Row)

vRow = Sheets("Template").Cells(Rows.Count, "A").End(xlUp).Row + 30

Sheets("Template").Range("A" & vRow) = vCenter

Sheets("Template").Range("B11:O39").Copy Destination:= _
Sheets("Template").Range("B" & vRow & ":" & "O" & vRow + 28)

Next vCenter

End Sub
Rollin
 

Attachments

Kophee

Thread Starter
Joined
Nov 12, 2011
Messages
5
Rollin_Again, thank you so much for responding to my call for help! The code you provided worked effortlessly and saved me a tremendous amount of time. I had braced myself for a day full of manual copying/pasting to complete a project that was due at the end of the day. Because of your code the project was finished by mid-morning. The code will come in handy for future use.

THANK YOU!!

Kophee
 
Joined
May 26, 2011
Messages
255
Kophee,

We all learn for experience. Sorry I did not respond quickly. Thanks too Rollin. That being said in the future could you provide a sample that would clarify what you want. The code I use was based on the "Sample" you provided.
 

Kophee

Thread Starter
Joined
Nov 12, 2011
Messages
5
CDHarm,

Please accept my apology for not having the actual template when I first posted. I reached out to the site over the weekend and was not in the office. The original example was in-line with what I was trying to accomplish and the code provided worked, however, I ran into trouble when I tried adopting the code to the actual excel file. Definitely user error.....lol.

I'm so grateful to you, Rollin and all who are part of the Tech Support Guy community. You help so many by sharing your expertise. Again, thanks for the assistance.....
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top