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.
Using InputBox Method to Select Range from Other Sheets
I need to select a range of cells from a second workbook via a InputBox or similar.
I'm trying to do that with the following
Sub InputBoxTest()
Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8)
MySelection.Select
End Sub
But I can't select a cell range if it is located in other workbook.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies View RelatedI 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 am getting this error when trying to generate the link report ....
Debug tells me that this makes reference to a sheet is not there , I have checked and it seems to be present
It is an urgent issue as it is preventing me to generate link reports...
I have a worksheet with a title in cell A1, followed by some range of numbers in the same column. I was wondering if anyone can suggest an easy method for selecting various numbers? What I originally thought would be easy was to put check boxes in the adjacent column of the numbers, allowing the user to select specific cells at will. This has turned out to be a nightmare and far too difficult for me.
I attached a sheet with the code I was originally working on, but I totally messed it up and it wipes out my data, but anyone cares to look it over, it's attached. So I humbly ask, how does everyone else do it? What is the norm for allowing a user to select multiple numbers at a time simply using a click from the mouse? How does one wright the code so that the selected values can be manipulated in further code?
I am having trouble assigning the range given by an inputbox method to the variable targetcells, and subsequently selecting targetcells. any help would be most appreciated.
Dim i As Integer
Dim CopyCells As Range 'the cells into which links will be copied
Dim TargetCells As Range 'the cells that copycells will be linked to
Dim CopyAddress As Variant 'the address of the copycells
Dim TargetAddress As Variant 'the address of the targetaddress
i = 0
For i = 1 To 100
Set CopyCells = Application.InputBox("Click on the cells to copy", "Automating copying links", Type:=8)
Set TargetCells = Application.InputBox("Select the target cells", "Automating copying links", Type:=8)
TargetCells.Select 'this line doesn't work for some reason (why?)
TargetAddress = ActiveWorkbook.Name 'this and the above lines are the ones I am having most trouble with - assigning the name of the targetcells workbook
I need to force users to enter one of these words only (Year End) or (Qrt) using inputbox method.
Note that user must not leave the inputbox until he/she enter one of these words.
I have the following code in Destination.xlsm that is intended to:
request the user to select a multi-cell range in a column of single-sheet Source.xlsm, in which some but not all cells contain "Y" (to indicate that this row of data relates to a National Account, versus a Territory Account)loop through the selected range, and whenever a cell contains "Y", copy the entire row and insert it in Destination.xlsm above a cell named "rngDest".
Code:
Sub Copy_NationalAccounts_Rows()
Const Message As String = "Select the entire range containing National Account Y flags, then press Enter or click OK."
Const Title As String = "Copy National Accounts"
Dim rngSource As Range
Dim rngDest As Range
Dim r As Range
Dim c As Integer
Dim wbk As Workbook
[Code] ........
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 am trying to select a range using this method. I am indicating type:=8, but isn't the box itself supposed allow you to select a range on a sheet? I cannot do this.
My only thought is that I have disabled events and updating; could that be it?
Here is my delimma. I am using the standard InputBox for my users to input the number of errors found for each category in a record from a daily report. The problem I having is that every time the user types in data using the InputBox it overwrites the previous days numbers. I need to set up the InputBox code so that after the user has input the numbers for that day that the next time the InputBox is used it selects the next column to the right and continues to do this for each day.
View 4 Replies View RelatedI seem to be going round and round in circles with this, but I'm sure it should be easy.
I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:
Dim namesTotal As Integer
namesTotal = 2500
Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select
(According to the Microsoft website, this is supposed to be the way to do it?)
I have this:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED
Ok 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.
Select Specific Cells And Transfer This Data Into A New Row In A Different Worksheet, how can I do this in a macro?
I am trying to pull a select 6,048 rows out of 288,000 rows.
I am trying to select specific cells and transfer this data into a new row in a different worksheet. The code i am using i used sucessfully on a user form to take the entries from text boxes and place in a row in a new worksheet. I would like to try and do the same thing but on a worksheet rather than a user form. My code is
View 2 Replies View RelatedI am trying to select a group of cells in an inactive worksheet from code in a module but I keep getting a run-time error 9 message and I cant seem to find the syntax problem.
I pasted my code below and the error occurs in the last line before the end sub statement.
[Code].....
I have created a spreadsheet that is 38mb and needed to size it down. I recorded the following macro to do this. This essentialy jut copies the top line of the relevant columns on each page and then copies the formulas down, calculates, and then pastes just the values of these calculations, thereby reducing the sheet to a third of its size. The macro works if I run it from the macro option on the toolbar, but when I assign it to a command button I keep getting ' select method
View 13 Replies View RelatedWhen the public routine ChangeColours is called from a Command button called ButtonX on SheetX it works fine. But a command button called ButtonY on SheetY cannot run it. When ButtonY is clicked, the error is at the line :
Sheets("SheetX").Cells(4,5).Select
Where it says that "Select Method of Range class failed"
Private Sub ButtonX_Click()
Call ChangeColours(0)
End Sub
Private Sub ButtonY_Click()
Call ChangeColours(0)
End Sub...............
I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
I'm getting "1004 select method of range class failed" on the Range statement below. This code is preceded by a number of range selection and formula-setting statements, nothing unusual.
Worksheets("summary").Select
Range("B5").Select
I have a macro that opens a specified woorkbook that changes every month. There are formulas which are pasted to range I1 of the new workbook to calculate the totals on this sheet. Everytime i run the code though, I get an error that says "Select method of range class failed" and Range("I1").Select is apparently the error.
View 7 Replies View RelatedI m trying to use some simple macro recording for a command button click. I keep getting the "Select method of range class failed" error from the following code.
From reading the forum it seems to be a common mistake by newbies, couldnt work it out for myself though...my command button is located on a different sheet to where the select function must work...from the code you can see that there are around 20 sheets that need to be used from the one command button...
Private Sub CommandButton1_Click()
Sheets("Treviso").Activate
Range("G21:T21").Select
Selection.ClearContents
Range("G39:T39").Select
Selection.ClearContents
Range("G61:T61").Select
Selection.ClearContents
Range("G77:T77").Select
Selection.ClearContents.....
Why I'm getting the above error when I try to copy and sort data into a workbook?
I'm using this:
Code:
Sub GetData1()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
[Code] .......
To import the data and then these lines to copy the unique records to a range:
Code:
Sheet33.Range("C1").Select
Sheet33.Range("C1:C1000").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Sheet33.Range("S1"), Unique:=True
But the above lines are highlighted when I get the error.
I am trying to copy set of values in a particular row as per the condition (Value in cell (1,2) = -40). find the code below:
Sub Mohan()
Dim Row As Integer
Sheet1.Activate
[Code]....
Macro has been working fine for ages suddenly have received this consistently?
It's not debugging to allow me to see the route of the problem.
i've googled and seen it's normally a use of select but can't see any issue in my code or understand why it would work for months and now stop.
note: also after i click Ok on the error the macro seems to continue running and data is pulled through. ...but this message comes up every time so it's questioning the reliability of the data it's pulling.
When I try to run following code, I get error
Run-time error '1004': Select method of Range class failed
Workbooks("Book1").Worsheets("Sheet1").Range("A1").select
however if I split the code like below it works perfectly
Workbooks("Book1").Worsheets("Sheet1").Select
Range("A1").select
I have used the macro recorder to generate the select range and clear part of the following macro.
Private Sub CommandButton2_Click()
Dim test As Worksheet
Sheets("Industry").Copy After:=Sheets(Sheets.Count)
[Code]....
I get the runtime error 1004 - Select method of range class failed.
I have used similar script in other macros without error.
I am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.
