Macro runs on active sheet instead of specified

D

drluke

Active Member
Joined
Apr 17, 2014
Messages
314
Office Version
  1. 365
Platform
  1. Windows
I am want to run a macro on a specific sheet by pressing a command button that is in another sheet. The code I have is:

Sub FormatData ()
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False

the rest of the code after that.

The problem is that the macro runs on the active sheet (ie the sheet the button is on). I've looked at all the previous posts about this but, as there seems to be a different answer every time this question is asked, I am very confused as to how to correct/change my code so that the macro runs on the correct sheet.

Any help would be appreciated.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hello,

Just add the below mentioned code in your macro and it will ask you the sheet name on which you want to run a macro. Please ensure to copy it AFTER 'Application.ScreenUpdating = False'

Dim ShtName As String
ShtName = InputBox("Please Enter Sheet Name")

Sheets(ShtName).Activate

Thanks,
Lucky
 
Upvote 0
If you are using a variable (as you are in post number one) then to answer your question we need to see how you are using ws in the rest of your code.
 
Upvote 0
MARK858 said:
If you are using a variable (as you are in post number one) then to answer your question we need to see how you are using ws in the rest of your code.
Click to expand...

I am not using a variable
 
Upvote 0
ws is a variable! If you aren't using it later in your code then that would be the likely issue
 
Last edited:
Upvote 0
drluke said:
I am want to run a macro on a specific sheet by pressing a command button that is in another sheet. The code I have is:

Sub FormatData ()
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False

the rest of the code after that.

The problem is that the macro runs on the active sheet (ie the sheet the button is on). I've looked at all the previous posts about this but, as there seems to be a different answer every time this question is asked, I am very confused as to how to correct/change my code so that the macro runs on the correct sheet.

Any help would be appreciated.
Click to expand...

Could you please with us the complete code, as even I want to know where you are using ws variable
 
Upvote 0
Lucky_Xl55 said:
Could you please with us the complete code, as even I want to know where you are using ws variable
Click to expand...

....especially as because the OP is using a variable in the original code there should be no need to activate the sheet with the slowing down the code issue that causes.
 
Upvote 0
MARK858 said:
....especially as because the OP is using a variable in the original code there should be no need to activate the sheet with the slowing down the code issue that causes.
Click to expand...

Code: 
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False
Dim ShtName As String
ShtName = "NewData"


Sheets(ShtName).Activate


    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "F.Type"
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Cells.EntireColumn.AutoFit
    Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.AutoFilter
    Columns("H:H").Select
    Selection.NumberFormat = "dd/mm/yyyy;@"
    Columns("N:N").Select
    Selection.NumberFormat = "0%"
    Columns("O:O").Select
    Selection.Style = "Comma"
    Columns("P:P").Select
    Selection.Style = "Comma"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "T.Type"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Jnl"
    Range("R2").Select
    ActiveSheet.Range("$A$1:$Q$2644").AutoFilter Field:=12, Criteria1:= _
        "Total Spend"
    Rows("2469:2644").Select
    Range("I2469").Activate
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$Q$2642").AutoFilter Field:=12
    Range("L1").Select
    Cells.Find(What:="LGRP %", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Range("A2469").Select
    ActiveCell.FormulaR1C1 = "LGRP"
    Range("A2469").Select
    Selection.Copy
    Range("B2470").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2470:B2642").Select
    Range("B2470").Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2469").Select
    Range("A1").Select
    ActiveSheet.Range("$A$1:$Q$2642").AutoFilter Field:=2, Criteria1:= _
        "Client Code"
    Rows("2469:2469").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$Q$2641").AutoFilter Field:=2
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "ESPO"
    Range("A2").Select
    Selection.Copy
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A3:A2468").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Selection.End(xlToRight).Select
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "ACR"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "Y"
    Range("Q2:R2").Select
    Selection.Copy
    Range("P3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("P3:R2641").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("Q2").Select
    Lr = Range("B" & Rows.Count).End(xlUp).Row
    Range("S1").Value = "Itm Code"
    Range("S2").Value = "SLMANFEE"
    Range("B1:R" & Lr).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("S1:S2"), CopyToRange:=Range("B" & Lr + 1), Unique:=False
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveWindow.SmallScroll Down:=9
    Range("O2643").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("P2643").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("O2643").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(LEFT(RC[-13],3)=""CBC"",(RC[1]/0.02),(RC[1]/0.01))"
    Range("O2643").Select
    Selection.Copy
    Range("O2644").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, operation:=xlNone, _
        skipblanks:=False, Transpose:=False
    Range("O2643").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    Range("A2643").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "WMC"
    Range("A2643").Select
    Selection.Copy
    Range("B2644").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2644:B2812").Select
    Range("B2644").Activate
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.Range("$A$1:$S$2812").AutoFilter Field:=1, Criteria1:="="
    Rows("2642:2642").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$S$2811").AutoFilter Field:=1
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("K1").Select
    Selection.End(xlToRight).Select
    Range("R1:R2").Select
    Selection.ClearContents
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=""0""&RC[-13]"
    Range("R2").Select
    Selection.Copy
    Range("Q3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("Q3:R2811").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    Range("E2").Select
    Selection.End(xlToRight).Select
    Columns("R:R").Select
    Selection.Delete Shift:=xlToLeft
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Man Fee Value"
    Range("O2").Select
    Range("A1").Select
    Sheets("INPUTS").Select
    Range("G13").Select
    With Worksheets("INPUTS").Range("G13")
        .Font.Name = "Wingdings2"
        .Font.Size = "20"
        .Font.Bold = True
        .HorizontalAlignment = xlHAlignCenter
        .Value = Chr(82)
        .Parent.Select
    End With
End Sub
 
Last edited by a moderator:
Upvote 0
There is a fair bit of recorded code to plow through to tidy up which I won't have time to do tonight but might have a go at it if I get bored during the week :biggrin:

But in the meantime it is as I said, you are setting up a variable and then not using it so all the code is acting on the activesheet.

All the time you are using Select and Selection then the sheet has to be the active sheet as Select can only be used on the activesheet.

In the meantime where you have put in the new code you are duplicating the task so change

Code: 
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False
Dim ShtName As String
ShtName = "NewData"


Sheets(ShtName).Activate
to just
Code: 
Dim Lr As Long
Dim ws As Worksheet
Set ws = Sheets("NewData")
Application.ScreenUpdating = False
ws.Activate

and if nobody else posts anything I will try and post some updated code during the week.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnZ1156
  • Solved
Make the current worksheet the active sheet.
2
Replies
11
Views
520
RoryA
RoryA
D
  • Question
VBA Not Running
2
Replies
15
Views
675
dawnmichelle
D
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
208
mumps
mumps
S
  • Question
Running a macro in workbook now running some of the macro in the personal.xlsb file!
Replies
7
Views
189
Sallyprit
S
S
  • Question
Doubleclick on cell in Protected Sheet
Replies
4
Views
317
shoheiyuna
S

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top