Using InputBox Method To Select Range From Other Sheets
Jun 23, 2006
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.
View 5 Replies
ADVERTISEMENT
Aug 26, 2009
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.
View 9 Replies
View Related
Oct 28, 2008
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 Related
Jun 15, 2012
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] ........
View 5 Replies
View Related
Jul 23, 2008
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
View 9 Replies
View Related
Sep 30, 2009
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?
View 9 Replies
View Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Oct 31, 2007
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 Related
Oct 7, 2003
When 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...............
View 9 Replies
View Related
Mar 10, 2007
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
View 13 Replies
View Related
Oct 22, 2008
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 Related
May 22, 2009
I 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.....
View 3 Replies
View Related
Jan 27, 2013
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.
View 1 Replies
View Related
Sep 6, 2013
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]....
View 6 Replies
View Related
Jul 23, 2014
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.
View 4 Replies
View Related
May 12, 2006
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
View 9 Replies
View Related
Aug 22, 2012
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
View 3 Replies
View Related
Oct 11, 2012
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.
View 2 Replies
View Related
Jul 28, 2006
I have a userform that allows the user to view, modify or delete individual records (rows) of a worksheet in a workbook. If a particular item in a record is modified, a check occurs to ensure that the values for that item remain unique. All of that works exactly the way I need. The breakdown occurs when the records are then autosorted by a the values in a particular column. After the autosort, another column of values is copied and pasted to a different worksheet within the same workbook. For this to work properly, I must have the userform open along with the worksheet containing the records. If the userform is open with any other worksheet open, I get the following VBA error message "Run-time Error '1004' : Slect method of Range class failed". I need to be able to open the userform with any worksheet active and not experience this error when the autosort, copy/paste occurs. This is the code for the user form, the module for autosort, copy/paste and checking for unue values.
This is the sub in the worksheet with the records
'This checks for unique values
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LLoop As Integer
Dim LTestLoop As Integer
Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String
Dim smessage As String
'Test first 7 rows in spreadsheet for uniqueness
Lrows = 8
LLoop = 2
'Check first 7 rows in spreadsheet
While LLoop <= Lrows.........................
View 3 Replies
View Related
Apr 7, 2007
I get the following error: Run-time error '1004': Select method of Range class failed at the following code line:
Worksheets("Sheet1").Range("A6").Select
View 4 Replies
View Related
Aug 19, 2009
Newcomer to Excel 2007 but long time programmer for Excel 2003. I am having problems with code that I feel should work. Code fails at the Select command with a "Run-time error '1004': Select method of Range class failed:
View 3 Replies
View Related
Oct 31, 2011
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.
View 1 Replies
View Related
Jan 15, 2010
I get an error message on the "Range(lstRow).Select line. (Method 'Range' of Object '_Global' failed). My goal is to transfer a specific range (C24:H24) to the first available row in Data Entry sheet.
View 2 Replies
View Related
Sep 1, 2006
this is some real simple code but for some unkown reason it keeps failing on me..
Sheets("All Work").Select
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
ActiveSheet.Paste
Sheets("All Work").Select
The second Cells . select doesnt work for some reason when i attach it to a button on the work sheet and i get the message
"runtime error "1004"
select method of range class failed"
but when i run just the macro it all goes smooth.
View 6 Replies
View Related
Jun 5, 2014
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
View 2 Replies
View Related
Jul 18, 2009
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 Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 Replies
View Related
Aug 9, 2007
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...
View 9 Replies
View Related
Oct 7, 2009
Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:
View 2 Replies
View Related
Aug 9, 2007
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?
View 2 Replies
View Related
