Selected Sheets Macro

[elblanco]

I select varying sheets in my workbook and run the macro LastPriceSheets below. B47 of each sheet either contains a price or just 0. When 0, I want to skip the price copy and move on to Next. The problem is the copy happens every time and not skipping on 0.

Sub LastPriceSheets()
Dim sht As Worksheet
For Each sht In ActiveWindow.SelectedSheets
Call LastPrice
Next
Wrapup:
Sheets("Last-Price").Select
End Sub
-----------------------------------------------------------------
Sub LastPrice()
Start:
If Range("$B$47").Value = 0 Then
GoTo Wrapup
Else
End If
MovePrice:
Range("$B$47").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10").Select ' <-- Before running, manually change("Bnn") to corresponding month when month changes.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Wrapup:
Range("$A$3").Select
Application.CutCopyMode = False
End Sub

------------------------------------------------------------------------------------------------------------------

 

[Alex Blakenburg]

Ideally you want to pass the sht variable to the Sub and it should all be rewritten to remove references to Select and Activate. Let us know if you want to see how that would look.
A quick and dirty would be to add the line below before Call LastPrice

    sht.Activate

 

[elblanco]

Ideally you want to pass the sht variable to the Sub and it should all be rewritten to remove references to Select and Activate. Let us know if you want to see how that would look.
A quick and dirty would be to add the line below before Call LastPrice

    sht.Activate

Thanks guys, but I'm halfway to "confused". I probably didn't code it right, but I got a compile error: Wrong number of arguments or invalid property assignment on the sht.Activate statement and the word Activate was highlighted. How would it look if i rewrote it based on your recommendations?

Sub Dosomething()
Dim sht As Worksheet

 = rich
    For Each sht In ActiveWindow.SelectedSheets
        sht.Activate

Call LastPrice
Next
Wrapup:
Sheets("Last-Price").Select
End Sub

 

[elblanco]

Thanks guys, but I'm halfway to "confused". I probably didn't code it right, but I got a compile error: Wrong number of arguments or invalid property assignment on the sht.Activate statement and the word Activate was highlighted. How would it look if i rewrote it based on your recommendations?

Sub Dosomething()
Dim sht As Worksheet

 = rich
    For Each sht In ActiveWindow.SelectedSheets
        sht.Activate

Call LastPrice
Next
Wrapup:
Sheets("Last-Price").Select
End Sub

Sorry but still getting compile error sht.Activate statement

 

[Alex Blakenburg]

I can't replicate your error.
Do you know how to use the immediate window ? Press Ctrl+G in the VBA window if you can't see it.
Copy the code below using the copy button in the right top corner and paste it in your module.
When you run it what is the last sheet name that prints in the immediate window before it errors out.

Sub LastPriceSheets()
    Dim sht As Worksheet
    For Each sht In ActiveWindow.SelectedSheets
        Debug.Print sht.Name
        sht.Activate
        Call LastPrice
    Next
Wrapup:
    Sheets("Last-Price").Select
End Sub

 

[Alex Blakenburg]

An alternate approach would be something like this:

Sub LastPriceSheets_v02()
    Dim sht As Worksheet
    For Each sht In ActiveWindow.SelectedSheets
        'Debug.Print sht.Name
        'sht.Activate
        Call LastPrice_v02(sht)
    Next
Wrapup:
    Sheets("Last-Price").Select
End Sub


Sub LastPrice_v02(sht As Worksheet)

With sht
Debug.Print .Name
Start:
    If .Range("$B$47").Value = 0 Then
        GoTo Wrapup
    End If
MovePrice:
    .Range("$B$47").Copy
    ' <-- Before running, manually change("Bnn") to corresponding month when month changes
    .Range("B10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                SkipBlanks:=False, Transpose:=False
    
Wrapup:
    Application.CutCopyMode = False
End With
End Sub

 

[Alex Blakenburg]

An alternate approach would be something like this:

Sub LastPriceSheets_v02()
    Dim sht As Worksheet
    For Each sht In ActiveWindow.SelectedSheets
        Call LastPrice_v02(sht)
    Next
Wrapup:
    Sheets("Last-Price").Select
End Sub


Sub LastPrice_v02(sht As Worksheet)

With sht
Start:
    If .Range("$B$47").Value = 0 Then
        GoTo Wrapup
    End If
MovePrice:
    .Range("$B$47").Copy
    ' <-- Before running, manually change("Bnn") to corresponding month when month changes
    .Range("B10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                SkipBlanks:=False, Transpose:=False
 
Wrapup:
    Application.CutCopyMode = False
End With
End Sub

PS: You are only copying values from 1 cell to another so this would do the same thing as your copy paste

    .Range("B10").Value = .Range("B47").Value

 

[elblanco]

An alternate approach would be something like this:

Sub LastPriceSheets_v02()
    Dim sht As Worksheet
    For Each sht In ActiveWindow.SelectedSheets
        Call LastPrice_v02(sht)
    Next
Wrapup:
    Sheets("Last-Price").Select
End Sub


Sub LastPrice_v02(sht As Worksheet)

With sht
Start:
    If .Range("$B$47").Value = 0 Then
        GoTo Wrapup
    End If
MovePrice:
    .Range("$B$47").Copy
    ' <-- Before running, manually change("Bnn") to corresponding month when month changes
    .Range("B10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                SkipBlanks:=False, Transpose:=False
 
Wrapup:
    Application.CutCopyMode = False
End With
End Sub

PS: You are only copying values from 1 cell to another so this would do the same thing as your copy paste

    .Range("B10").Value = .Range("B47").Value

OK, I don't get the compile error anymore. It runs through completion on the four sheets I selected but the If zero is not skipping the copy. Its copying on every sheet. BTW, I appreciate your help.

 

[Alex Blakenburg]

Do you have a formula in B47 ?
If so what is the formula ?

 

[elblanco]

Do you have a formula in B47 ?
If so what is the formula ?

no, it's either a numeric value or zero.

The correct values are getting copied on each non-zero sheet.