Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro for each sheet (2003)

    Hello Everyone,

    I am trying to create a macro that would apply to each sheet in a worbook. Below is a list of steps I need to take:
    1. unprotect each sheet (password for all sheets: L&C).
    2. Insert 3 columns at the beginning of each sheet
    3. copy cell d4 and paste in A6 for each sheet
    4. copy cell F4 and paste in B6 for each sheet
    5. copy cell K4 and paste in C6 for each sheet
    6. Unmerge cell K5 and G24 through N24, and G76 through N76 for each sheet
    7. copy cell D5 and paste in A7 though A22, and A57 through A70 for each sheet
    8. copy cell F5 and paste in A7 through A22, and A57 through A70 for each sheet
    9. copy cell K5 and paste in A7 through A22, and A57 through A70 for each sheet
    10. Insert new sheet call "Recap" infront of workbook
    11. copy rows 7 through 22, 24, 57 through 70, and 76 for each sheet
    12. paste rows into "Recap" sheet.

    Any help doing this would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    You can use code like the following - you will have to complete it yourself:

    Dim wsh As Worksheet
    Dim wshRecap As Worksheet
    Set wshRecap = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbo ok.Worksheets(1))
    wshRecap.Name = "Recap"
    For Each wsh In ActiveWorkbook.Worksheets
    If Not wsh.Name = "Recap" Then
    wsh.Unprotect Password:="L&C"
    wsh.Range("A:C").Insert
    wsh.Range("D4").Copy Destination:=wsh.Range("A6")
    ...
    ...
    End If
    Next wsh

    Note: in step 12, you haven't specified where you want to paste the rows.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    Thanks Hans, I will go ahead and complete the macro. For step 12, I wanted to insert it in cell A2 of the recap, but I will try to put it together and post if I have any trouble.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    But if you paste from each sheet to cell A2 in Recap, each new paste will overwrite the previous one. Don't you want to paste below the previously pasted data?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    Yes. You are correct. I need to paste below the previous data.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    If you need help when you get there, post back.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    I need a little bit of help. I finish the macro, what is the command to paste the rows 7 through 22, 24, 57 through 70, and 76 to the "recap" Sheet. Below is the macro.

    Sub formatsheets()

    Dim wsh As Worksheet
    Dim wshRecap As Worksheet
    Set wshRecap = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbo ok.Worksheets(1))
    wshRecap.Name = "Recap"
    For Each wsh In ActiveWorkbook.Worksheets
    If Not wsh.Name = "Recap" Then
    wsh.Unprotect Password:="L&C"
    wsh.Range("A:C").Insert
    wsh.Range("D4").Copy Destination:=wsh.Range("A6")
    wsh.Range("f4").Copy Destination:=wsh.Range("b6")
    wsh.Range("K4").Copy Destination:=wsh.Range("c6")
    wsh.Range("j5,G24:N24,G76:N76").UnMerge
    wsh.Range("d5").Copy Destination:=wsh.Range("a7:A22,A57:A70")
    wsh.Range("f5").Copy Destination:=wsh.Range("b7:b22,B57:B70")
    wsh.Range("k5").Copy Destination:=wsh.Range("c7:c22,C57:c70:")


    End If
    Next wsh
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    Try this:

    Sub FormatSheets()
    Dim wsh As Worksheet
    Dim wshRecap As Worksheet
    Dim lngRow As Long
    Set wshRecap = ActiveWorkbook.Worksheets.Add( __
    Before:=ActiveWorkbook.Worksheets(1))
    wshRecap.Name = "Recap"
    lngRow = 1
    For Each wsh In ActiveWorkbook.Worksheets
    If Not wsh.Name = "Recap" Then
    wsh.Unprotect Password:="L&C"
    wsh.Range("A:C").Insert
    wsh.Range("D4").Copy Destination:=wsh.Range("A6")
    wsh.Range("F4").Copy Destination:=wsh.Range("B6")
    wsh.Range("K4").Copy Destination:=wsh.Range("C6")
    wsh.Range("J5,G24:N24,G76:N76").UnMerge
    wsh.Range("D5").Copy Destination:=wsh.Range("A7:A22,A57:A70")
    wsh.Range("F5").Copy Destination:=wsh.Range("B7:B22,B57:B70")
    wsh.Range("K5").Copy Destination:=wsh.Range("C7:C22,C57:C70")
    wsh.Range("7:22,24,57:70,76").Copy _
    Destination:=wshRecap.Range("A" & lngRow)
    ' Increase lngRow for next sheet
    lngRow = lngRow + 32 ' 32 is the number of rows copied
    End If
    Next wsh
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    Hans,

    I get an error in the following line.
    wsh.Range("7:22,24,57:70,76").Copy _
    Destination:=wshRecap.Range("A" & lngRow)
    Can it be because I don't have a column reference.? What I would like to do is to copy those rows from each sheet into the "recap" sheet. The recap sheet would have all the rows from each sheet.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    Sorry, I referred to the source rows incorrectly. It should be

    wsh.Range("7:22,24:24,57:70,76:76").Copy _
    Destination:=wshRecap.Range("A" & lngRow)

  11. #11
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    Hans. One last question. I get the following error display (see attached). Is there a command I can put so I don't have to say yes or not. Thanks.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for each sheet (2003)

    You could insert a line

    Application.DisplayAlerts = False

    at the beginning of the macro, and

    Application.DisplayAlerts = False

    at the end.

  13. #13
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for each sheet (2003)

    Thanks again.

Posting Permissions

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