I have a macro that duplicates a hidden page, and names the duplicate sheet by cell reference. then the macro goes back and resets the forms that were used, what i would like to do is have it end selected on the newly created sheet, but i can't reference the sheet because i don't know in advance what the sheets name will be.
View 5 RepliesI have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
I have a workbook with a Cost Analysis sheet, a master menu sheet, and then all the sub sheets. I have coded command buttons on the master sheet, so once a category is selected, it goes to the appropriate sub sheet. On the subsheet is a list of items of varying sizes, and command buttons beside that which will allow the user to "Select" that particular size. Once the size is selected, the verbage in cell B5 plus the verbage (size) beside the selected amount, and the prices (labor and materials) in the next two columns, need to be transferred to the first available blank line, starting with line C5, to the Cost Analysis sheet. I have never done coding before for a Loop (i.e. For i = ???) and am not sure how to get it to do what I want. After they select that, they can pick a command button that takes them back to the master menu so they can continue picking various items until they are through. Then the Cost Analysis sheet will show all the items they picked, (name of item, material cost and labor cost: Columns B, C, & D). Not everything is coded yet (such as selecting a command button to go back to the main menu, because I wanted to make sure I could solve this problem before doing the easy stuff).
I read over quite a few of the previous threads and am not sure how to change them to suit my circumstance. I know some code (VB), but not much. I do not understand the concept of DIM, and how the loop works to make it happen myself, and all the websites I could find were not suited to my situation of copying three cells of data to the first blank cell on another sheet.
is it possible to hide a sheet automatically if i select the other sheet. For example, I have sheet1, sheet2 and sheet3, I'm at sheet2 and if i select the sheet1, sheet2 and sheet3 will automatically hides.
View 4 Replies View Relatedif I change the name of my sheet throught this code, how I can selected again? ...
View 9 Replies View RelatedI have written this code;
VB:
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 22 And Target.Count = 1 Then
Sheet("Graphs data ref").Unprotect
Sheet("Graphs data ref").Range("E1:E1").Select
Sheet("Graphs data ref").Protect
[Code]....
The problem is the first code isn't working, it is running the macro whenever any cell is selected and not refreshing the auto filter on my table.
I hope I have provided enough information on here, if I haven't let me know and I will reply as soon as I can.
I have a sheet that have 100 checkbox. i want to select(not check) all checkbox for change their property.
How can I select all? (by macro or other way)
i have a close workbook with path ("Z:42766Jan 2 Dec 2014Tally.ERP9GrpSum.xls") from another open workbook i want to popup a inputbox to select the sheet and run the macro on selected sheet.
e.g
i put 2 in inputbox than run the macro on sheet2
I'm trying to select 1 out of 9 checkboxes on one sheet, first checkbox located in Cell#I22 and when it's selected, I would like it's name ("ROC") of that checkbox to appear on second worksheet in cell # I23.
View 3 Replies View RelatedIs there a simple bit of code which I can add to a button which will always select the next tab along?
Looking to add a 'next tab' button at the top of each sheet so it can be run through quickly, almost as a presentation.
I could just do
Sheets("TabName").Select
But that would involve doing a new one for each sheet, and would need modifying if I then re-ordered the sheets.
Below is a macro I'm trying to create. The basically it's supposed to be a quick simple macro to save lots of time. I want to select current tab, move/copy (create copy) to the end....then select the tab I've just copied and copy paste special the values (then rename the tab to todays date) FYI the tab I'm copying is called rebuy shipping.
However, I want to then do the same at a later date with the most recent tab, but with the below code it always selects and copies the original tab:
Sub macrorebuy1()
'
' macrorebuy1 Macro
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("Rebuy Shipping").Select
[Code] .....
I assume this is a simple error. I've tried inserting Activesheet.previous.
So far my line of code is:
Sheets(" & Cstr(y) & " - " & Cstr(i) & " load" & ").Select"
Selecting a worksheet with 2 variables in it and some text,where y and i are integer variables. P
I have a workbook with multiple sheets , final sheet has a data of all sheets , in the first sheet i want to select what results to be shown in that final sheet ,, specially the week and the LDM
View 12 Replies View RelatedI have a workbook that uses Autofilter.
Column A has data. Column B is hidden, has no data, and is empty.
Colmn C has data, on through the rest of the sheet (to Column AB).
Why column B has no data is unimportant for now. It, however, must maintain its existance and cannot be deleted.
When I select the autofilter sort button on column A, it selects only that column, and not the rest of the sheet. Data is subsequently scrambled.
When I unhide the empty column B and select the sort button on column B, it selects the entire sheet. Data remains intact.
When I select the sort button on column C, it selects the rest of the sheet, but not column A (with data), or B (with no data). Data is subsequently scrambled.
Using this code from Ron de Bruin to e-mail one sheet from many in a workbook. Three questions...#1 - how would I select and mail only the last sheet in the workbook or #2 - allow user to enter the unique sheet name (one of many) to select and mail only that sheet. #3 - Would like to put the unique sheet name in the e-mail subject line after "MIR #_____.
View 4 Replies View RelatedI am trying to set a macro that will delete a sheet which the spreadsheet user types the name of the sheet into a dialog box rather than go to the actual sheet and delete the sheet.
View 3 Replies View RelatedI have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
[Code] .......
I seem to running into dead ends with each previous post concerning this similar idea, despite brave efforts of kind people . . .
So here is another thought: As the title suggests, can one help me with code that would take the value in (say) Sheet1A1 (Say Harry) and use that value to search for a sheet with that name (Harry) in the same workbook and then link to that sheet, cell A1 (Harry!A1)?
The other posts are here, should you want to know, but they differ as far as the linking method goes:
Post 1, Title: Newly created Tab Names placed as Hyperlink on Content Sheet
Newly created Tab Names placed as Hyperlink on Content Sheet
Post 2, Title: Automatically redirect hyperlink when cell value changes
Newly created Tab Names placed as Hyperlink on Content Sheet
I am trying to select all unlock cells in the active sheet. However, my method checks through each cell in the sheet and it takes too long.
Sub SelectUnlockedCells()
Dim rng As Range
If IsNull(Cells.Locked) Then
'if null then there is unlocked cells
For Each c In cells
If c.Locked = False Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
Else
End If
If rng Is Nothing Then Exit Sub
rng.Select
End Sub
I have a workbook with rental properties containing 2 sheets. 1 sheet with the general details and the rate sets and 1 sheet where I'm trying to calculate the total price for a period of time between 2 dates. I would like the people who use this workbook to simply enter the dates and get a price based on the amount of days within a rate set.
eg: 1 to 7 days is weekly, 8 to 31 is monthly etc. To display this, I'm using: =LOOKUP(H3,{0,7,30,180,365;"daily","weekly","monthly","6 months","yearly"})
These rates are different for each property, and this formula isn't exactly dynamic; it just displays which rate should be used.
Now my idea is to use IF/THEN kind of a formula, but I'm lost on how to do this. The actual rates are on the other sheet in separate rows and columns belonging to the appropriate property. Also, the rates as they are will probably need to be recalculated to a daily number before it can be used for the price calculation, because a month has either 29, 30 or 31 days.
At the moment the daily rate is diplayed as a daily rate
The weekly as a weekly rate
The Monthly as a monthly rate
The 6-Monthly as a Monthly rate (with a minimum of 6 months of course)
And the yearly also as a Monthly rate (with a minimum of 12 Months)
I've attached the sheet as a reference : Property details import.xlsm
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
Here is the original code from the previous post.
I'm trying to create a macro that will allow the user to select a range of cells, (in column V in the attached sample), and then insert a new worksheet and copy the entire row where the user selected the cells to that worksheet.
For example: (I've deleted most of the data from the sample). The user want to create an order that has an exteded cube of 300 feet. By holding the "Ctrl" key down, they could pick and choose the parts they want to add, until the sum in Excel shows nearly 300.00 extended cube. They would then run the macro, which would copy only the rows which the user has selected in column V to the new worksheet.
I want to activate a sheet in the workbook.
Sheet names: "Sheet1", "Main"
When I write the code
Sheets("Sheet1").select 'fails
Why is this? This was the command that was from the record macro feature. I am using excel 2010.
Excel 2010
My macro cleans up data in worksheet "Chalkboard".
After cleanup, I'd like to copy data and select the last sheet in the workbook (the one on the far right) and paste the data.
The last worksheet changes monthly: Jan Feb Mar Apr, etc.... Meaning each month, I'll add a new worksheet for the given month.
I need code that will select the last worksheet in the workbook.
Im sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select
View 5 Replies View RelatedI am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the
sheets( range("c5")).select
to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
Why is the below code not valid? I am using a userform with a cancel button and i cant it to copy and paste some lines on sheet Chart_Data as it unloads. But i just get the error
"Runtime Error '1004': Method 'Select' of object '_Worksheet' Failed"
Private Sub Cancel_Click()
Chart_Data.Select
Chart_Data.Rows("6:7").Copy
Chart_Data. Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Unload Me 'Cancels Operation
Exit Sub
End Sub
I use "ActiveWindow.ScrollWorkbookTabs Position:=xlFirst" to display all available sheets in a workbook. But can the worksheet of the first tab (i.e. at the far left) be automatically selected after the workbook is opened?
View 6 Replies View RelatedThe macro below in its current state adds data entered from the userform to a specific sheet. I would like to change it so that a player can be selected from combobox named txtmplayer and the data entered be copied to that specific players sheet.
If this information is vital: There are 8 players. Player sheets can be named player1, player2, and so on. I would like to keep same method for entry (finds first available row)