Fill across sheets - except one?

[123excel]

Hi, i´m using the macro below to copy data from one sheet to all other sheets. It works great but i would like to exclude one page named statistics. Anyone who knows how to do it?

The macro:

Dim ws As Worksheet
    Sheets.FillAcrossSheets ws.Range("F5:H5")
    Set ws = ThisWorkbook.Sheets("NewAgency")

 

[Trebor76]

Hi 123excel,

You could just clear the range from that tab after the data has been put there:

Option Explicit
Sub Macro2()
    
    With ThisWorkbook
        .Sheets.FillAcrossSheets ThisWorkbook.Sheets("NewAgency").Range("F5:H5")
        .Sheets("statistics").Range("F5:H5").ClearContents
    End With

End Sub

Another (though in my humble less efficient) way to do it would be loop through each tab but ignore the one called statistics.

Regards,

Robert

 

[123excel]

Hi Trebor76! I can´t clear the range, cause there is other data on the statistic sheet that would be erased. How do you loop throught each tab? I also forgot to mention that new tabs/sheets are added time to time (through another macro), so i cant make a loop with a fixed number of sheets.

 

[123excel]

Hi, i tried another macro. This macro is doing the job, every page is changed except Statistics and Info, but at the end i get "object_worksheet failed" error, and it stops the macro to keep going (more actions are done after this part of the macro). It is sh.Select that gets yellow marked when running the debugger. Any ideas what could be wrong? I have some hided sheets, maybe thats the cause? Even if so, I dont want to make them visible again..

Sheets("NewAgency").Activate
    Range("F5:H5").Select
    Selection.Copy
    Dim sh As Worksheet
    
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "Statistics" And sh.Name <> "Info" Then
        sh.Select
    
    Range("F5").Select
    ActiveSheet.Paste
        
         End If
Next sh

 

[Trebor76]

Hi 123excel,

Yes the problem is that you're trying to select a tab that's hidden. In the majority of cases you don't need to select a tab to work with it.

See how this goes:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False

    For Each wsMySheet In ActiveWorkbook.Worksheets
        If wsMySheet.Name <> "Statistics" And wsMySheet.Name <> "Info" Then
            Sheets("NewAgency").Range("F5:H5").Copy Destination:=wsMySheet.Range("F5")
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[footoo]

Or if you don't want to paste to the hidden worksheets :

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "Statistics" And sh.Name <> "Info" And sh.Visible Then _
        Sheets("New Agency").Range("F5:H5").Copy sh.Range("F5")
Next sh

There no need to turn off ScreenUpdating - the code doesn't involve any screen updating.