Run Macro Only When One Of 2 Sheets Are Active
Dec 10, 2011
I have a macro that runs when the workbook is saved, it starts to enter in certain formulas onto the worksheet, how can I have this maro run only when 2 specific sheets are active
So basically I want the macro to run if either worksheet "Sales Jan-Jun" is active or worksheet "Sales Jul-Dec" is active, if they are not the active sheets I want to exit sub
This is what I tried without success
If ActiveSheet ("Sales Jan-Jun") Or ActiveSheet ("Sales Jul - Dec") Then
Exit Sub
Else
View 8 Replies
ADVERTISEMENT
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.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.
View 4 Replies
View Related
Sep 25, 2007
I have a big spreadsheet with a number of sheets, some with dynamic lists which take up a lot of processing power, but which I only use rarely. Consequently the whole spreadsheet is running very slowly. Is there a way to set certain sheets to not automatically update, but retain automatic updating on others? I know you can set Calculation to Manual in the Options, but I need to retain automatic calculation on some sheets.
View 9 Replies
View Related
Nov 27, 2008
I have a rather big worksheet with lots of sheets in it. I need to see in the frontpage how many sheets there are in total (-2, the frontpage & end sheet don't count) and how many sheets are currently shown (ie. not hidden, as there are vba-filters which sheets to display).
I don't really mind how it is shown, it could be one cell with 14/53 meaning 14 sheets shown of a total 53 or in seperate cells.
View 4 Replies
View Related
Oct 22, 2011
I am trying to select a variable amount of worksheets in the workbook and print just those.
I was trying to do something like...
Code:
Sub print_sheets()
shtcount = Sheets("Master List").Range("A278")
For i = 2 To shtcount
Sheets(Array("i")).Select
Next i
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
...but this wasn't really working...
View 3 Replies
View Related
Feb 8, 2012
Is there a code I can use that will only have autocalc functioning on the active sheets? If not, how about for only the current Files that are active?
View 1 Replies
View Related
Nov 1, 2008
I have a workbook that has a sheet for every day of the month (1 to 31).
When it opens it automatically activates the sheet for the day corresponding today.
What I'm missing is how to enable users to only update that sheet.
Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.
View 9 Replies
View Related
Aug 20, 2009
Is there any way to only print active sheets
i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets
i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it
View 9 Replies
View Related
Nov 27, 2008
I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?
View 2 Replies
View Related
Jul 12, 2012
I have a large workbook where the tab colors differ. I don't want the first few sheets or the last dozen or so to sort but I want to be able to activate/group numerous sheets and then run a macro that will take those sheets and sort them by color.
View 4 Replies
View Related
Jun 8, 2006
is it possible to keep the same active cell when switching sheets in the same workd book. I am in sheet1 and cell C15 is my active cell. When i move to Sheet2 it either defaults to A1 or the last place i was in this sheet. I guess it would be the Worksheet_activate() and deactivate function but not sure
View 3 Replies
View Related
May 8, 2014
That particular question has been solved, but now i need it to work with multiple values from combobox.
So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
Run-time error '-2147417848 (80010108)': Automation error
The object invoked has disconnected from its clients.
When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate
View 5 Replies
View Related
Aug 20, 2014
code to pull up all the sheet from closed workbook to active opend workbook.
Closed Workbook name : Create Position
Active Workbook name : EIB builder
View 1 Replies
View Related
Apr 21, 2009
I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.
View 5 Replies
View Related
Mar 18, 2009
I have written the below macro to copy data from a sheet called "Fixit Example 1" to a sheet called "Fixit Summary Example". It works fine, but I'd like to be able to run it from any "Fixit Example XX" worksheet. As written, the data is always copied from "Fixit Example 1" regardless of what worksheet I have open.
I tried replacing Windows("Fixit Example 1.xls").Activate with Windows(Active.Worksheet).Activate but got an error "Run time error 424. Object required."
Sub MoveData()
'
' MoveData Macro
' Macro recorded 18/03/2009
View 9 Replies
View Related
Jan 10, 2010
Can't seem to get started with this one. I've got a workbook with several worksheets. I want a macro to run BeforePrint but only if both these conditions are true:
Wsheet "BEN SHEET" is active
Value of A1 on BEN SHEET = value of B2 on BEN SHEET
If both true, I want MsgBox with question asking "XXX ?". If Yes, print, if no, no action.
View 9 Replies
View Related
May 22, 2014
My code and see why it's not executing properly?
[Code] .......
View 7 Replies
View Related
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
Aug 21, 2009
Using vba how do I tell a macro to select the row that the active cell is in?
I'm just using a basic delete Row macro but I'd like for the macro to automatically select the entire row when it's time to delete instead of me highlighting the section.
View 2 Replies
View Related
Oct 18, 2012
i have the following macro that copies data from sheet2, sheet 3, sheet 4 and consolidates it into "master" sheet.
Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ofs As Long
If Sh.Name = Sheet2.Name And Sh.Name = Sheet3.Name And Sh.Name = Sheet4.Name Then
ofs = CLng(Mid(Sh.Name, 2)) - 1
Sheets("Master").Range("C9:BB400").Offset(ofs * 500).Value = Sh.Range("C9:BB500").Value
End If
End Sub
i am not well versed with macros
1) the macro is not going to sheet2, sheet 3, sheet 4
2) how can i make my range dynamic. for example, my range is C9:BB400.
-can i make it so the range is C9:BBxxx (where xx is the last active data cell in the range?)
it will prob take a pro a few seconds.
View 3 Replies
View Related
Dec 20, 2013
Is there a way a person can enter the active cell and have the cursor go to the upper left part of the cell and enter the current date and remain in that cell for additional data? More often than not there will already be data in the cell but sometimes it will be blank.
View 7 Replies
View Related
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
Nov 14, 2006
Sub display_all_false()
With Application
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Control Toolbox").Visible = False
.CommandBars("Drawing").Visible = False
End With
End Sub
But, when I run the code, seems there're run-time errors detected at these codes:
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
1. Is there any way to correct these codes?
2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)?
3. Can the command menus (File Edit View Insert Format ...) be hidden also?
4. Does looping method for each worksheet
View 9 Replies
View Related
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
Oct 24, 2006
writing a macro that will refresh all the active cells in my "Master" worksheet.
Every month, I append data from one Excel worksheet to the end of my Master worksheet. I want all the cells in the Master worksheet to be formatted as text. However when I copy the cells, I get different formats, i.e. text, general, number, etc. Simply selecting the new data and applying the text format from the Format Cells popup, does not work. I know I need to "re-enter" all the data for the new format to take effect but I really don't want to F2-> Enter thousands of cells.
how to write a macro that will refresh all active cells in a worksheet?
View 4 Replies
View Related
Jul 24, 2007
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores.
I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck...
Sub Hide_Scores()
Range("B15:EU35").Select
ActiveSheet. Unprotect
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
View 6 Replies
View Related
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
Apr 5, 2006
Can a macro start from wherever cell the active cursor is on? or
perhaps from the cell that the macro button is clicked?
What I am trying to do is copy the data from F(whatever) to whatever
the last empty cell is in the row holds the active cursor. Or I can
create buttons for the macro to run, and it could copy the information
in that particular row.
View 10 Replies
View Related
Feb 14, 2012
I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)
I want to be able to run this macro from any cell on the line above. How to modify it?
Code:
Sub Open_Hyperlink()
'
' Open_Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+o
'
[Code]...
HTML Code:
ABCDEFGHIJKLMNO
1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip
2XLinkData112/21/2011BobSmithData2Data3Data4123 MainMooresvilleNC28117
3XLinkData112/22/2011LarryJonesData2bData3Data4456 MainMooresvilleNC28117
4XLinkData112/23/2011MaryAkinData2Data3Data4789 MainMooresvilleNC28117
An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"
View 2 Replies
View Related
Jul 31, 2012
I have a list of names in coloum D of a worksheet called "Trial", I have another worksheets called "Usage" with this list. I need a macro which can search the name written in Trial worksheet in Usage and worksheet, and activate the found results in usage worksheet.
I cannot use vlookup formulae here as "trial" sheet only has last names, which "usage" sheet has full names.
View 1 Replies
View Related
