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.
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 RelatedI 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")............................
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?
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("sheet2").Select
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("sheet2").Select
Worksheets("sheet1").Shapes("Button 1").Select
With Worksheets("sheet1").Shapes("Button 1")
.Characters.Text = "Done"
End With
End Sub
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 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?
Sub AddIFERROR()
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 & ","""")"
[code]....
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 Relatedthe 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
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 RelatedI 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.
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
Code:
Sub SalPiv()
'
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick
[Code].....
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.
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")
[code]....
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
Next
Application.ScreenUpdating = True
End Sub
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
VB:
Sheets("Front").Select
Range("E1").Select
Selection.End(xlDown).Select
Range("E3").Select
ActiveCell.Formula=INDIRECT(ADDRESS(MATCH(9E+99+306,*****ActiveSheet.Name*****!F:F,1),6,4))
The macro has to work whis way becuse I will be using it with multiple worksheets, all with different names.
What is the key word sequence to cause VB code to execute on a worksheet change?
View 9 Replies View RelatedMy 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 RelatedIs 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......................
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?
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
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 ActiveSheet.name but that doesn't seem to work.
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] .....
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.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "O6" Then
If Range("testCell").Value = 8 Then
Range("P10:AD27").Select
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Range("O6").Select
[code].....
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 RelatedI 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?
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?
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
HideCol
End If
End Sub
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? ............
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?
Original
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
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?
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