1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Copy & Insert Macro - Excel

Discussion in 'Business Applications' started by Kophee, Nov 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Kophee

    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.
     

    Attached Files:

  2. CDHarm

    CDHarm

    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.
     

    Attached Files:

  3. Kophee

    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.
     
  4. robosys

    robosys

    Joined:
    Nov 14, 2011
    Messages:
    1
    thats a very good help the code ids so much useful, i have to enter the huze data of one product printer ink , it will help me
     
  5. Kophee

    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.
     

    Attached Files:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     

    Attached Files:

  7. Kophee

    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
     
  8. CDHarm

    CDHarm

    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.
     
  9. Kophee

    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.....
     
  10. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026545

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice