Macro Code To Change Active Worksheet

May 12, 2006

I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed.

View 4 Replies


Macro To Change Name Of Active Worksheet?

Mar 27, 2014

Say i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?

View 2 Replies View Related

Macro Code Only Works If Specific Worksheet Active

Apr 30, 2008

I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:

Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................

View 2 Replies View Related

Code To Stop A Worksheet Selection Change Macro Running

Aug 17, 2009

I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.

Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?

View 6 Replies View Related

Change/Modify/Add Text In Shape On Non-Active Worksheet

Sep 7, 2006

I have a button on say, sheet1 with text that I want to change after a certain action takes place; however, I was trying to change this text without switching sheets. (switching sheets isn't a big deal, I'm mainly trying to do it my way for the practice). I'm able to select the button on the other sheet without actually switching sheets, but when it comes to changing it's text I get an error. Here is the code I tried:

Sub macro1 ()
Worksheets("sheet1").Shapes("Button 2").Select
Selection.Characters.Text = "Done"
End Sub

Problem with this is it gives cell A1 on sheet2 the "done" text. I also tried this...but it gives the error.

Sub macro1 ()
Worksheets("sheet1").Shapes("Button 1").Select
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
End With
End Sub

View 3 Replies View Related

Macro To Change Active Window

Apr 27, 2009

I'm trying to enter this very simple macro that is supposed to switch from one active window to another. However, I'm getting an error, which I can't quite figure out. Here is the

View 2 Replies View Related

Macro / Change To Run On Active Cell Instead Of Whole Page

Apr 24, 2013

I am still kind of new to using VBA code and my editing skills are very novice at the moment.

How would I change this Code to run on only the active cell selected and that is all, instead of the whole page?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim xCell As Range
Dim xFormula As String
For Each xCell In Selection
If xCell.HasFormula Then
xFormula = Right(xCell.Formula, Len(xCell.Formula) - 1)
xCell.Formula = "=IFERROR(" & xFormula & ","""")"


View 6 Replies View Related

Making Worksheet Active With Macro

May 12, 2006

Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.

View 5 Replies View Related

Advanced Filter Active Sheet Macro Code

May 9, 2008

the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?

Sub myfilt()
Application. ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

View 3 Replies View Related

Rename Active Worksheet From Personal.xls Macro

Mar 12, 2009

I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.

View 4 Replies View Related

Macro To Filter Table Using Active Worksheet Name?

Jun 7, 2013

I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:

ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"

If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.

View 1 Replies View Related

Macro To Create Pivot Table On Active Worksheet?

Jun 20, 2012

I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from

Sub SalPiv()
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick


View 1 Replies View Related

Excel Macro (VBA) Sending Active Worksheet In PDF By Outlook

Mar 17, 2014

I find out a code to create a PDF (with opening the Save As dialog box) from an active worksheet, but I can't find out how to send this PDF by e-mail (Outlook). The code is working till the words 'Set OutApp'.

Just what I want is to send the active worksheet as PDF (as attachment) by email (Outlook). Here the present code.

Sub SendPDF()
' SendPDF Macro
Dim OutApp As Object
Dim OutMail As Object
Dim v As Variant
v = Application.GetSaveAsFilename(Range("E2").Value, "PDF Files (*.pdf), *.pdf")


View 9 Replies View Related

Macro: Process The Cells For Every Worksheet In A Book Rather Than Just The Active One

Apr 2, 2007

I need to get this macro to process the cells for every worksheet in a book rather than just the active one

Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Macro - Paste Formula Into Another Worksheet Using Active Sheet Name As Reference

Jan 9, 2014

I'm using a macro to paste a formulae into a destination cell on another worksheet.

The problem is that I need the Macro to put the name of the active sheet into the formulae


The macro has to work whis way becuse I will be using it with multiple worksheets, all with different names.

View 9 Replies View Related

VB Code Execution On Worksheet Change

Sep 16, 2009

What is the key word sequence to cause VB code to execute on a worksheet change?

View 9 Replies View Related

Code For Button Errors Due To Worksheet Name Change

Oct 15, 2008

My code works well if the worksheet name is Sheet1. It will change constantly. I have alot of other similar workbooks that are not affected and cannot figure this out. Here is the code that I get the error at: ....

View 9 Replies View Related

Change Worksheet Change To Macro

Oct 23, 2008

Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then
Select Case Target.Value
Case "1"
Target.Font.ColorIndex = 20
Target.Interior.ColorIndex = 10
Case "Good"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 35
Case "Stable"
Target.Font.ColorIndex = 2
Target.Interior.ColorIndex = 27......................

View 9 Replies View Related

Worksheet Change Macro Takes Too Much Time When Run With Update List Macro

Feb 1, 2009

I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View 5 Replies View Related

Macro With Reference To Active Chart On Active Sheet

May 14, 2014

I currently have the following macro running to set a chart's data values:

Sub C3Quarter12013()
' C3Quarter32013 Macro
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = _

[Code] ......

When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.

I've tried changing the sheet name to but that doesn't seem to work.

View 4 Replies View Related

Macro To Change Year In VBA Code

Oct 31, 2013

I have a macro called pull that contains a year several times -see sample code below

I woulsd like a macro that will prompt and enable me to change the year from say 2013 to 2014 on the Macro called "Pull" . I intend to set up button to link to the macro

With Workbooks("Fruit & Veg Dept Commisions.2013.per Budget.xls")
With .Worksheets("Service")
Set cfind = .Cells.Find(what:=CDate(mmonth), lookat:=xlWhole)
.Cells(9, cfind.Column) = x

[Code] .....

View 2 Replies View Related

VBA - Run Macro On Worksheet Change

Jan 25, 2013

I have a sheet called Summary. On that sheet, Cell O6 has a drop down with two options, when you change these options, a number of other cells on the same sheet automatically change (just using formulas). Including a cell that I've given the named Range of 'testCell'. Based on the drop down, test cell will either = 8 or 9.

What I also want to change is the format of a range of cells whenever O6 is changed - but only when O6 is changed.

However, the following code does not work. It works fine if i remove the 'If Target.Address = "O6" Then ...' but doesn't work with it included.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "O6" Then
If Range("testCell").Value = 8 Then
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"


View 2 Replies View Related

Change Event Code To Run Macro When A Cell Value Changes

Mar 21, 2007

I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...

View 4 Replies View Related

Worksheet Change Macro Activation?

Oct 28, 2012

I have a worksheet change macro that from the insertion of date (dd/mm/hhhh) in a cell, fills some other cells with the day number, the week number, the month,...

Everything works fine when i fill one date after the other. But when I try to fill many cells with the same day by dragging the black cross at the right corner of the cell the macro is not activated. how to activate a worksheet change macro by dragging the black cross as a mean to fill many consecutive cells in a column?

View 6 Replies View Related

Macro Change The Name Of The Worksheet It Copies

Sep 27, 2008

i created a sheet and called it "Original" (which somehow is a template), in another sheet i created a big Button named "Create account" ,then i created a macro which copies that "Original" sheet and by default it names it "Original 2 " now that i need a new account each time i click that button to create a new sheet and rename it by the client name i need, in order to keep the "Original" intact as a template. It happens sometimes by mistake that i rename the "Original" one ( The template one) which therefore returns an error when i click the button!

Is there anyway to add to that macro a code which rename that "Original 2" something else lets say "Account" to differentiate it from the Template one?

Or to Protect only the "Original" sheet from being renamed out of the whole sheets i have , in order not to rename it accidentally?

View 9 Replies View Related

Running Macro When Change Made To Worksheet

Feb 16, 2012

The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Range("C10")
If Not Intersect(Target, changed) Is Nothing Then
End If
End Sub

View 3 Replies View Related

Macro Needed For Worksheet Change Event

Jul 31, 2008

In Col D of my spreadsheet, I have a list of security codes, in this list there is a security code "all", i need a macro that will add 1 to the code, so it reads "all1", now i need the macro to run as soon as new data is pasted to sheet "Lending", the ranges in Col D do change on a daily basis therefore cell reference for security codes is not fixed.

Can this be achieved? ............

View 9 Replies View Related

Worksheet Change Macro Runs Differently

Dec 3, 2008

When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?

Sub sessions()
Dim a As Integer
Dim x As Long
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row

Sheets(1).Unprotect Password:="password"

For a = 2 To x

If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = True
ElseIf Cells(a, "m") Cells(a, "d") Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = False

End If

Next a

View 9 Replies View Related

Set Textbox Control Source To Hidden Worksheet NOT Active Worksheet?

May 6, 2014

I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.

I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.

The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.

how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?

View 3 Replies View Related

Change Order For Chart Series Collection Macro Code

Mar 18, 2008

I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?

With cht
.ChartType = xlColumnClustered

' first data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

' second data series
Set ser = .SeriesCollection.NewSeries
With ser
.Values = someValues
.XValues = someValues
End With

View 2 Replies View Related

Copyrights 2005-15, All rights reserved