Loop Through Multiple Sheets in a Workbook, Excluding a selected few, and make the same change to each.

[RadheyaMansel]

Hi guys,


This is a very simple code, but I'm not sure why it isn't working.

I want to loop through each worksheet in a workbook and make the same change to each: Copy a range and paste the figures into the first available empty row starting in Column C.

At the moment, when I play this Macro there aren't any errors, it just loads for about a minute but when I check the sheets after it's finished, nothing has happened.

Note: I have a macro that just selects all the sheets as a group, and makes the change, but the problem is that it doesn't past the value in the last available row if they are different for each sheet, it seems to find the furthest down free row and then paste into that for all of the sheets.


A little help would be great,

Thank you!

   Sub WorksheetLoop()


  Application.ScreenUpdating = False
 
         ' Declare Current as a worksheet object variable.
         Dim wks As Worksheet
         Dim Loc As Long


         ' Loop through all of the worksheets in the active workbook.
         For Each wks In Worksheets
         
         
         'Exclude the named sheets below, coupled with End IF
        
          If wks.Name <> "Ing_Index" And wks.Name <> "Daily Production" And wks.Name <> "StoreToDecanting" Then
              
              
          
       Range("H20:J20").Select
       Selection.Copy
    
       Loc = Range("C" & Rows.Count).End(xlUp).Row + 1
        Range("C" & Loc).PasteSpecial xlPasteValues
    


           
         
          End If
      Next wks


      Application.ScreenUpdating = True






      End Sub
[\Code]

 

[mikerickson]

The problem is that you aren't qualifying your range references.
Try

For Each wks In Worksheets

    If wks.Name <> "Ing_Index" And wks.Name <> "Daily Production" And wks.Name <> "StoreToDecanting" Then
       
        wks.Range("C" & Rows.Count).End(xlUp).Offset(1,0).Resize(1,3).Value = wks.Range("H20:J20").Value

    End If
Next wks

 

[RadheyaMansel]

Thank you mikerickson, it worked! It still takes about a minute to complete, is that simply down to processing power, and the amount of sheets (150) that it has to loop through? or is there any way I could clean up the code?

Thanks again,
Radheya

 

[mikerickson]

One minute is a bit long. Is there any other code in that loop? Did you remove all use of Select?

 

[RadheyaMansel]

My laptop is just very slow, I tried it on a colleagues computer and it ran almost instantly.