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
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 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.
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.
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
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
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.
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
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.
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.
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
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.
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
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.
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?)
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.
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?
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
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.
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?
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
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.
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.
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?
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"
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.
