I have a spreadsheet that lists values for different grid lines. On top of each set of data, I have labels each line "Line A", etc. I want to put in possibly a pull down menu at the top of the sheet that the user could use to jump to whichever line they were interested in viewing. For example, if the user needs data from Line L, they could select Line L from the drop down menu and the spreadsheet would jump down to that area.
I have a workbook with over 100 worksheets (all worksheet tabs are named). Since the tabs are not sorted in a numerical order (they are grouped by departments hence the numerical tab order does not apply), can I search for a particular worksheet tab using a search function? The CTRL F function doesnt allow searching for worksheet tabs. At the moment, I have to go through the tab names in order to find one particular tab.
'Get all the sheets 'y = 3 Sheets(1).Select Worksheets.Add Sheets(1).Name = "OM Files" For J = 2 To Sheets.Count With Sheets(J).UsedRange x = Sheets(J).Name If x "xxx" Then .Resize(.Rows.Count).Copy _ Sheets(1).Range("A65536").End(xlUp)(3) End If End With Next Application.DisplayAlerts = False
I am all the time doing something like this. Is there a formula or something of that nature that would sum every 6th cell down starting a a given point?
I'm having real trouble selecting a series of rows based on variables for the purpose of cutting them and pasting them elsewhere in the spreadsheet. For example, if this is my spreadsheet
row 2 I Hate 3 Bill 4 O'Reilly
I want my code to select the range of 2:4, cut them, and paste them elsewhere but I want 2:4 to be variables. For example
Fun = 2 Stuff = 4
Rows(Fun:Stuff).Select Selection.Cut
But doing "Rows(Fun:Stuff).Select" does not work at all. If you do Rows(Fun).Select, this works to select Row 2, but I can not use the variables to select a range.
Sheets("Oven Codes").Select Rows("3:3").Select Selection.Delete Shift:=xlUp Sheets("Item Edit").Select Range("I12:J12").Select What I would like to do is have VB look to E1 to determine which row to delete. E1 could say 5 or 25.
I want to freeze the formulas (F4) in whole column in Microsoft Excel, Is it possible to freeze together, not to freeze one by one. I freeze it one by one but that's actually a pain when you have hundreds of formulas to freeze.
I'm trying to add a button with an assigned macro to email specific info from the active sheet and submit to the email address on the same sheet.
E4 would be the email subject and would include a generic text body but I would also like it to capture the data in cell D22 and include this below the generic email text body.
The To: email address is found in I14 and will require a CC from range Q14.
I have a spreadsheet of several sheets, but 7 of them (Red, Orange, Yellow, Green, Blue, Purple, Black) are the guts of the file and where I make adjustments. The layout of these sheets is the same, there are several cells of data on each sheet that coordinate with data in the same cell on other sheets. The problem is the sheets are so big, when I change sheets I have to go find the data that was in the same cell (or close) to where I just was on a previous sheet. So for example if on “Red” I am in cell AX253 and I go to sheet “Yellow” I would like it if AX253 would be selected when I get there. And if I select a different cell like D56 on Yellow and pick Green then I want D56 to be automatically selected on Green.
In addition, I plan to use a check box on an eighth sheet called “White” to turn the feature on and off. I have never written code for a check box before.
Is this to terribly difficult to do? I did some searching and saw some code that was similar to this but could not get it to work.
Ok, so I'm a little cheezed that I've been volun-told to give up an economic model that took me over 8 months to develop to a few investment bankers that will likely sell it to the highest bidder. Of course, with no credit to me.
I've already taken a few of the necessary steps to lock it down.
Examples:
- Lock/hidden cells along with sheet protection (with password) - to prevent viewers from seeing the formulas I used.
- protect workbook (with password) - to prevent copy/paste of the sheets
- VBA - Tools - VBA Project Properties - Protection - Set password - to prevent viewing of the macros
- Hiding sheets using "Very Hidden" in VBA - to prevent viewing of sheets in general.
I guess my real question is, when this is all said and done, how easy is it for someone to break through all the passwords that I've set up in the workbook? If it's ridiculously easy, how do I prevent it (if at all)? What else can I do?
I have entered VBA code and tested it with success. If I then save & close the excel file and then re-open it, the mask time entry is not saved (time does not appear correctly after input) and per instructions I have designated (and am using) the cell input range.
I was wondering if there was an easy way to copy columns (headings) and the formulas to a new sheet. I'm currently working on Sheet 1, need to copy everything onto a new sheet so I can keep things separated by week..
Example, Sheet 1 is Week October 30th, would like my 2nd sheet to have same exact headings and formulas which I would then rename to Week Nov. 1st etc..
Also, is there a way that I can then copy the 4 sheets that would make up a month into a new spreadsheet so I can then start December...
I am trying to calculate weighted average cost for a list of thousands of items.
I will try to explain this as best I can.
Column A is item. Column B is Mode. Column C is Volume. Column D is Cost
There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.
I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.
how to deduct a percentage off of a range of units.
For example, if I have 100 units that cost $50, I'd like to deduct 20% off of the first 10 units, and then have the total dollar amount put into a separate cell.
So, 100 x $50 = $5000
With 20% off of the first 10 units: $4900. __________________________
I have been working on my spreadsheet for sometime now, so far when I run into a code problem I can figure it out using someone eles's post. However, I can't seem to figure this one out. I need to send data from a userform to specific cells on my spreadsheet based upon the users selection in combobox 1, and textbox 1.
Example: User selects customer name from Combobox1, and part number auto loads into textbox1 from the data sheet.
There are then 11 combobox's that can be clicked as the userform is updated. Once the user is finished, I need the answers from each combobox to transfer to the worksheet next to the referenced Combobox1 and textobox1.
I used the code that RoyUk posted to him, but have only been able to get the first combobox to copy to the sheet, the rest stay blank.
(Here is the code so far)
Private Sub CommandButton2_Click() Dim ce As Range, srcRng As Range Dim sYear As String, sMonth As String
sYear = UserForm3.ComboBox1.Text 'When combobox1 is loaded, use as reference#1 sMonth = UserForm3.TextBox1.Text 'When textbox1 is loades, use as reference #2 Set srcRng = Range("c2", Range("c65536").End(xlUp)) 'Search range on worksheet For Each ce In srcRng
I am trying to enter only the named ranges in the active worksheet into an Array. The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level. ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest() Dim sArray() As String Dim sJoin As String Dim y As Long
x = 1 y = ActiveSheet.Names.Count z = ActiveSheet.Name
I am created Financial Worksheets. . . ex. Balance Sheet, Income Statement, Cash Flows, Notes, Forecasted Sales Etc. and I want to make a GUI in-order to navigate easily. I already included Hyper Links to it for some detailed annexes.
I am trying to edit the Copy menus so that they only paste values. I have the following code in the workbook object:
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window) Application. CommandBars("Edit").Controls.Item("Paste").OnAction = "New_paste" Application.CommandBars(" Cell").Controls.Item("Paste").OnAction = "New_paste" End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window) Application.CommandBars(1).Reset End Sub
and this code in a module:.........
The module code is however bringing up an error message 'User-defined not defined' and I am not sure why.
i was stuck with some mass data which spans over many rows and columns. i am relatively new to excel and wanted to know how i can get this data arranged in a way where i can access all the data from a printable view. i have attached a file explaining basically what i want.
If ActiveWorkbook.Sheets.Count < 17 Then Application.CommandBars("Workbook tabs").ShowPopup Else Application.CommandBars("Workbook tabs").Controls("more Sheets...").Execute End If
The code doesn't do anything on my excel workbook which currently has like 20 workbooks, and it grows daily by like 4 or 5 worksheets.
I have a workbook with multiple worksheets. Each worksheet corresponds to a certain store fixture estimate. Ont these sheets I have a specific cell where you can input how many fixtures of that type are to be used.
On that sheet also, is a range of cells (ex. Range("A65:F3340")) that needs to be copied to a new summations sheet of total hours to build the project.
If sheet 1 has 1 fixture - the macro should copy the range of cells only once. Sheet 2 has 4 fixtures - tha macro should copy the same range four times appending each set of data tot eh end of the previous, And so on for each fixture sheet.
I have a form set up with excel (2003) using vba. I'm doing a check to see if textbox1 is empty, if it is, a msgbox pops up with a warning that "textbox1 needs to be filled out". I click OK and the cursor goes to textbox2. I want the cursor to return to texbox1 without user intervention but can't figure out how. I'm using "Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)". I know it can be done because I did it with vba Access, but I can't seem to find it using vba Excel.
I would like to have a "PREVIOUS" navigation button in a workbook that goes back to the previously activated worksheet, which may or may not be physically the previous worksheet in the workbook. In other words, if I go from Sheet #1, to Sheet #3, to Sheet #5, I'd like to return to Sheet #3 from Sheet #5 (rather then navigating back to Sheet #4).
"Open VB editor and double click 'Thisworkbook' and paste this code in on the right:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) lastsheet = Sh.Name End Sub
Then right click 'ThisWorkbook' and insert module and paste this code in
Public lastsheet As String Sub Select_Last() Sheets(lastsheet).Select End Sub
Whenever a user changes sheets, the name of the last sheet is now held in the variable 'Lastsheet' so if you assign a shortcut or button to the code then the last used sheet will be selected."
My problem is that when I want to assign the macro, I can't find macro name that looks anything like the above in the drop down list of Macro Names. how to assign the macro to a button?
I am trying to use the combo box feature to navigate between sheets. In other words, I have a dropdown menu at the top of each sheet ("Main Menu," "Goals," etc.) and am trying to work out a macro that directs the user to whichever sheet is selected. Is this possible?
