how do you select a worksheet using a cell value
in cell a1 i have a random number we shal say it is 57
in cell b1 the same we shall say it is 8
i need to select worksheet a1 and cell b1
sheet57!8
sheet(a1!b1)
If I want to select a cell in another worksheet I'm currectly doing this:
View 3 Replies View Relatedi know this is pretty lame but why do i get an error message when i use...
Sheets("Sheet2").Select
Range("A1").Select
the error message is run time error 1004, application -defined or object-defined error.
i want to activate cell A1 so that i can use it as a location for referencing another cell using offset.
I'm using the following code. It works fine when it comes to selecting the cell. However, I'm not sure how to bring the view of the worksheet such that the selected cell is in the middle of the sheet. I've the data horizontally laid out from column A to column BM. So, when someone makes a change in Sheet2 I need to point to the corresponding column in Sheet1 such that it is in the view.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellName As String
CellName = Range("A1").Value
[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 write a macro, in VBA, that would select the proper worksheet, based on the value of a cell.
View 3 Replies View RelatedI want to know how to find certain String and select the cell it on finding the String.
View 10 Replies View RelatedI need to select a worksheet from another workbook.
I want use a cell contains which contains a date that corresponds to the worksheet's name in the other workbook.
I'm using a button in my sheet to jump around to standard locations in each worksheet. Generally in the active worksheet I have these three buttons working perfectly.
However I have one button that takes you from the worksheet into the dashboard. The problem is if you had scrolled the dashboard around you may be put at your last place. I want the screen to jump to and center on R1C1
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies View RelatedI want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies View RelatedI need to write a macro that will start at worksheet A, then select the next worksheet, and the one after that, etc. Worksheet A will always be the same, but the following worksheets will always be different.
View 9 Replies View RelatedI think this may be a little difficult (for me at least!) but would it be possible to have a list of the open workbooks popup when I run my macro, so that I may select which workbook to run it on?
The macro itself just does some formatting of the sheet, but it is contained in a master file.
I attached my workbook so you guys can easily take a look.
In this workbook I have 2 hidden worksheets.
To start off, you click the Add Week button, put in a date (mm-dd-yy), that brings up the sheet thats named with the date you just put in the text box. (Which happens to be placed in the wrong spot) In that sheet that appears theres a button to "Delete last" which is suposed to delete the last sheet in the worksheet but the new sheet gets placed between the existing charts. Thats my first problem.
Next, when you hit the delete button, my VBA code is set to make one of the hidden sheets visible so it can delete one of the rows.
I am using the below code which works perfectly in same sheet. But instead of range a1 and b1 in the same sheet (bold one), i want to use range a1 and b1 of sheet2. Range "Nazim" is named range in sheet1. And this code will be put in sheet1.
View 2 Replies View RelatedI have a workbook with around 350 worksheets. A column in the main sheet contains a list of part numbers which correspond to the names of the worksheet tabs. When a cell on the main sheet containing a part number is selected I would like a macro to find the corresponding worksheet, move that worksheet to the end of the row of tabs and open it.
I am using excel 2007
I am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code:
Sub ImportAlarms()
Dim thisSheet As Worksheet
Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet
Set targetSheet = Sheets(TARGET_SHEET)
[Code] .......
I have a user form containing a drop down list and two text boxes. The drop down list contains at the moment 2 items (address 1 and address 2) I have two worksheets,1st named address 1 and 2nd address 2. So far I have it set up so that the information enter in to text box 1 and 2 plus whichever one of the address is selected from the drop down list is entered in to the next available blank row on the active work sheet. What I would like to achieve is when address 1 is selected from the drop down list all of information is added to sheet 1 in the next blank row, same for address 2, this would be enter into sheet.2
All of the above would run from a command button (Enter)
I have set a String Variable as a worksheet name in one workbook and I want to find that worksheet name in another workbook that I have opened using VBA to write data to it.
When I try to use the variable, it gives me a Run-time error 9 Subscript out of Range.
Part of my code
Dim strSheetName As String
strSheetName = ActiveSheet.Name { This is on the ist WorkBook}
Application.Workbooks("Customers-2008").Activate { This is on the 2nd WorkBook}
MsgBox ("Here is " & strSheetName & " "") { This is to see if strSheetName is passed} It is
This is where the problem is on the new Workbook
Sheets("strSheetName").Select
How can I select the value of strSheetName which is the sheetname that I want to select
why this works:
Worksheets("Sheet10"). Range("H9:i9").Select
whereas this does not
Worksheets("Sheet10").Range(Cells(9, 8), Cells(9, 9)).Select
How to Select All Objects On Worksheet?
View 2 Replies View RelatedThe last few days I have had great support in answering my questions. I would like to tweak the code one more step.. The RFQ LIST workbook grabs data from Inventory workbook after select the workbook.
This code in RFQ LIST allows to select the workbook, I would also like to select the workbook.sheet as found out they want to tab the data and keep long term history.
I have a userform that does search using Name. If there are multiple records found it displays a message "There are X instances of (value in name)" and shows a list box. When user selects Find All, the multicolumn listbox gets populated. Selecting a row in lisbox populates the corresponding values in the userform. Problem is that selecting a row in listbox fails to select the corresponding row in the worksheet. As a result, if i change the values in the textbox (say for eg. Phone) the changes are updated in the row selected on the worksheet but not on the required row! Pardon my innocense, I am very new to VBA. find the attached worksheet. This is not my work, I found it during research. Auto Merged Post;Sorry the previous file had error. find the updated worksheet.
View 4 Replies View RelatedI have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
I'm sure you're all familiar with Text Boxes:
You can add shapes and text boxes (text box: A movable, resizable container for text or graphics. Use text boxes to position several blocks of text on a page or to give text a different orientation from other text in the document.) to a worksheet by using the Drawing toolbar.
Did you know that if you click the text box button, then click on a spreadsheet (inserting a blank text box), then click off the text box, a small blank text box will remain hidden from view on your spreadsheet? Once hidden, the only way to find it is to slowly move your cursor over the Text Box edge, and watch the cursor momentarily change shape. Did you also know that if you copy or fill down the cells behind that text box, it will make as many copies of itself as you made for the cell? Not too dis-similar from a virus.
Imagine what would happen if you unknowingly had some of these text boxes on your main calculation sheet (200 columns by 2000 rows); one where you regularly copied formulae/cells for a period of over four years. Yes, (judging by their names e.g. "Text Box 29413") the thriving population of these invisible beasties is now in the neighbourhood of thirty thousand! The damn things are worse than fleas; and they're impairing the function of the whole workbook.
Help! Would someone please provide a VBA macro to find, select and delete these little devils from the spreadsheet (just this one spreadsheet)?
I have a worksheet with two dropdown menus. The first contains months from January to December, the second contains years from 2011 to 2025. Upon opening the worksheet, I would like the entries selected to match to the month and year of the current system date. I know this is relatively easy to implement but, unfortunately, the macro recorder is of no support here. How to refer to dropdown menus in an Excel worksheet and how to select a specific entry from the dropdown menu?
View 4 Replies View RelatedOk I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
I have all sheets selected:
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next
' The thing is that i now want to ungroup or select the first worksheet
Sheets(1).Select ?
And then run the same sub on all the worksheets by this:
Dim wSheet As Worksheet
For Each wSheet In Worksheets
Next wSheet
Debug error is:
Select method of worksheet class failed: Sheets(1).Select ?
I'm using an inputbox to let the user select a range of cells. Here's the line
Set MyRange = Application.InputBox("Select cells with numeric data", Type:=8)
When the inputbox comes up, it won't let the user click any cells (or anywhere in the worksheet, for that matter). The cell/range reference has to be typed in manually.