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.
View 3 RepliesI 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.
View 7 Replies View RelatedIs there an easy way to collapse rows and columns on all sheets in a workbook.
View 3 Replies View RelatedIs there an easy way to add every sixth column?
Example:
=A1+A6+A11+A16
Then
A2+A7+A12+A17
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.
This is the code I have so far:
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.
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 many files that has a filepath references in the cell values, for example:
='\serverfolder1subfolder2[Filename.xls]Sheet'!$B$82
Is there a way to change all the references to direct to another place?
Filenames don't change, only the '\serverfolder1subfolder2 path
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.
View 9 Replies View RelatedI 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.
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.
View 4 Replies View RelatedI 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.
View 5 Replies View RelatedYou could use:
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 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 want to create a floating window in excel that can contain a series of macro buttons (12 total)
I'd like to see if this window can be opened with a hyperlink or a single macro button on a spread sheet.
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?
View 9 Replies View RelatedWhat i want to do is create a drop down list in excel on a single sheet to navigate to a certain row of the worksheet.
I thought i coudl use data validation then use some code in a worksheet change event to skip to that section like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
Select Case Target.Value
Case "Test1"
Sheets("Sheet1").Range("A5").Select
Case "Test2"
Sheets("Sheet2").Range("A9").Select
End Select
End If
End Sub
Problem is it wasnt exactly what i was looking for as i cant use an array in data validation so what i thought was a good idea was a bad one.
what i would like is a drop down list that when i select a heading within it, it naviagtes me to that row where my heading is but also makes that row the row scroll up so it is at the top of the page....Hope this makes sense.
my row numbers are 4, 13, 19, 28, 37, 43, 52 62, 65 and 75.
i would like the other tabs to be still visible to the user (so not hidden), but they should not be able to go to those sheets. (clicking on their name should so nothing)
I'm going to be creating a workbook that contains a worksheet for each year. Within each worksheet, I will have a calendar format that contains a range for each month of the year (multiple columns and multiple rows). Is it possible to name areas based on month and then create a button or drop down list at the top that would allow users to select the month and the view would go to that area within the calendar?
View 2 Replies View RelatedI have 2 reports on one worksheet. I have put a button at the top of each report to navigate back and forth. One report is located at a1:S27 - it shows up fine. The issue is the other report located at BA1:BT52. No matter where I assign a name to it the report doesn't come up full page with cell BA1 at the top left hand corner.
View 3 Replies View RelatedI have a large table where rows are added by copying either the top row or the bottom row and adding it to the bottom of the table. Below is the coding I was hoping to use to jump from cell to cell within the rows using a hotkey assigned to the macro.
Is it possible to apply the following code in a way that copies the same hotkey targets from row to row?
In other words, a row copied from row 1 to row 2 would still have functioning targets of A2, D2, L2 and Q2.
Select Case ActiveCell.Address
Case Is = [A1].Address: [D1].Select
Case Is = [D1].Address: [L1].Select
Case Is = [L1].Address: [Q1].Select
Case Else: [A1].Select
End Select
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
Attached is a protected sheet. The yellow cells are unprotected. When the use is on the cell L3, and the right arrow is clicked I want it to jump to M6 and vice verser, in other words, navigate in order from left to right. Is it possiblwe to do this?
Split off from
Dependent/Linked Drop Downs In Cells
Originally Posted by Dave Hawley
Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?
Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick. Auto Merged Post Until 24 Hrs Passes;Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.