Solved: Run Macro on Specific Sheet

[maninjapan]

I am trying to run a macro on a specific sheet ( regardless of the active sheet at the time) and have attempted the following, however it applies the macro to whatever the active sheet is. I am assuming that this is the incorrect way to achieve this. Any help would be appreciated.

Sub Noukaibi()

With Worksheets("Gold")

If Range("B35").Value <= Range("L40").Value Then
Range("B37").Value = Range("L39").Value
End If
End With

End Sub

[Aflatoon]

Close:
Sub Noukaibi()

With Worksheets("Gold")

If .Range("B35").Value <= .Range("L40").Value Then
.Range("B37").Value = .Range("L39").Value
End If
End With

End Sub

Note the periods before the Range calls.

[maninjapan]

Thank you Aflatoon, all fixed!!

[rb100]

I am experiencing similar issues: I want to apply a macro to a specific sheet, but it is only being applied to the active sheet. I tried adding periods before the range calls (as mentioned above), but this resulted in errors for me. Any help is greatly appreciated!

Sub Number_Conversion_Macro()

With Worksheets("Sold to Breakout")



Range("B1").Select
Selection.Copy
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True


Range("d1").Select
Selection.Copy
Range("d3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True


Range("f1").Select
Selection.Copy
Range("f3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True

Range("h1").Select
Selection.Copy
Range("h3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True
End With

End Sub

[JKwan]

You are missing the period in front of all your commands

.Range("B1").select

If you don't fully qualify your commands, then it will be whatever your active sheet

[rb100]

You are missing the period in front of all your commands

.Range("B1").select

If you don't fully qualify your commands, then it will be whatever your active sheet

when I add the periods (see below), I receive the following error message: run-time error '1004' Select method of Range class failed





Sub Number_Conversion_Macro()


With Worksheets("Sold to Breakout")



.Range("B1").Select
Selection.Copy
.Range("B3").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True


.Range("d1").Select
Selection.Copy
.Range("d3").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True


.Range("f1").Select
Selection.Copy
.Range("f3").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True

.Range("h1").Select
Selection.Copy
.Range("h3").Select
.Range(Selection, Selection.End(xlDown)).Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=True, Transpose:=True
End With

End Sub

[JKwan]

Sorry, glanced too quickly. You have all those Select and Selection.... To quickly fix your problem add below to your code


With Worksheets("Sold to Breakout")
.Select

[rb100]

Sorry, glanced too quickly. You have all those Select and Selection.... To quickly fix your problem add below to your code


With Worksheets("Sold to Breakout")
.Select

that worked. Thank you very much!

[Paul_Hossler]

I am experiencing similar issues: I want to apply a macro to a specific sheet, but it is only being applied to the active sheet. I tried adding periods before the range calls (as mentioned above), but this resulted in errors for me. Any help is greatly appreciated!


1. It's better to start your own thread instead of replying to a 9 year old SOLVED one

2. If you use the [#] icon to insert CODE and /CODE tags you can paste your macro between them to format it and to set it off

[Paul_Hossler]

1. You usually don't need to .Select objects to use them. The macro recorded captures them, but you should clean them up


2. I'm not sure about the End(xlDown).End(xlDown) part, but without seeing the data, I have no suggestions



Option Explicit


Sub Number_Conversion_Macro()
With Worksheets("Sold to Breakout")

.Range("B1").Copy
.Range("B3").End(xlDown).End(xlDown).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=True, Transpose:=True

End With
End Sub