PDA

View Full Version : Solved: Run Macro on Specific Sheet



maninjapan
11-10-2011, 05:23 AM
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
11-10-2011, 06:09 AM
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
11-10-2011, 09:21 AM
Thank you Aflatoon, all fixed!!

rb100
12-07-2020, 07:18 AM
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
12-07-2020, 07:50 AM
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
12-07-2020, 08:10 AM
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
12-07-2020, 08:22 AM
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
12-07-2020, 08:36 AM
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
12-07-2020, 09:10 AM
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
12-07-2020, 09:22 AM
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