Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    11

    Unanswered: Edit Recorded Macro to Apply to Multiple Sheets

    Hi - I'm a novice at using EXCEL Macros. I'm trying to create a macro whereby I copy ovetime hours, overtime pay, and the organization code within each worksheet to a new worksheet (on a new line) so that I can capture all overtime hours and pay for each organzation in one sheet. All of the data are located in the same cells within each worksheet. I created a recorded macro to copy the data from one sheet into a new sheet. However, I now need to edit the macro (in Visual Basic) so that the process repeats itself for each sheet in the workbook. I think I need a "Do ... While ... Loop" to carryout the macro for all sheets in the workbook until there are no more sheets. I'm trying to develop this macro so that I can repeat this process every pay period. Any help that anyone out there can provide would be much appreciated. Thanks in advance.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy and welcome to the board. If you are a newbie to VBA, then I would suggest using looping with "For Each Worksheet" in workbook rather than Do ... While loop. Also, do you have a naming scheme for the worksheets?

    Also, if you want to add at the bottom of the new worksheet, then you will need to determine the next blank row. Something along this line

    Code:
    Dim NextRow as Long
    NextRow = Worsheets("NewSheet").Cells(Rows.Count,1).End(Xlup).Row + 1
    Then you can use NextRow to determine where to paste.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2007
    Posts
    11
    Rich - Thanks for the response. Will try what you suggested.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •