Free Microsoft Excel 2013 Quick Reference

Trouble Using InputBox Method to Select Range from Other Sheets

TROUBLE USING INPUTBOX METHOD TO SELECT RANGE FROM OTHER SHEETS

Hi,

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 code:

Code:
--------------------
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.
Can somenone point me to the right direction.

Thanks
P&V

--
pao_e_vinho
------------------------------------------------------------------------
pao_e_vinho's Profile: http://www.excelforum.com/member.php...o&userid=21360
View this thread: http://www.excelforum.com/showthread...hreadid=554915


Trouble Using InputBox Method to Select Range from Other Sheets

Hi,

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 code:


	VB:
	
 InputBoxTest() 
    Dim MySelection As Range 
    Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) 
    MySelection.Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I can't select a cell range if it is located in other workbook.
Can somenone point me to the right direction.

Thanks
P&V

Trouble Using InputBox Method to Select Range from Other Sheets

Hi,

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 code:

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.
Can somenone point me to the right direction.

Thanks
P&V

Dear all

I have an userfrom with refedit control in it. I can easily select any range on the excel where the form resides, however now i would like to use this excel as templete with an ability to fetch ranges from other excel sheet..

Kindly let me know how to acheive this..

ex.. i have opened the excel with refedit control in it. Along with it i have opened 2 more excel sheets. Now i would require the refedit control to select any range among these 3 excel sheets.

Thanks in advance.

I am trying to copy data that is displayed on the Monthly Master Sheet and paste it to the correlating Month Sheet. I want for the user to be able to put in the "Month"/Sheet Name in a inputbox to paste the data to that particular sheet. I can't figure out how to select just a sheet from an inputbox. I have tried the following code:


	VB:
	
 InputBoxTest() 
    Dim MySelection As Range 
     
    On  Error Resume Next 
    Set MySelection = Application.InputBox(prompt:="Select a range of cells", Type:=8) 
    With MySelection 
        .Parent.Parent.Activate 
        .Parent.Activate 
        .Select 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Which allows me to select a sheet and cell range but after that my copied data is lost also I would like for the function of the inputbox to just select a sheet name, no cell ranges. I want to use my coding to select the cell ranges. Do not want the user to have to/be able to select cell ranges just input the sheet name.

I've beat my head on the keyboard trying to get this to work.

I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:


	VB:
	
 Button2_Click() 
    Sheet7.Activate 
    Run "HorizontalPrintStuff" 'generic landscape pagesetup
    With ActiveSheet.PageSetup 'specific pageset settings
        .RightFooter = "Construction Assumptions" 
        .PrintArea = "CONSTRUCTION" 'the named range to print
        .Zoom = False 
        .FitToPagesTall = 1 
        .FitToPagesWide = 1 'this changes depending upon the page selected
    End With 
    ActiveSheet.PrintOut 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.

So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.


	VB:
	
Sheet3.Activate 
 'Sheet3.ResetAllPageBreaks              'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
    .PrintArea = "DEVBGTALL" 'the named range to print
    .FitToPagesWide = 4 'this changes depending upon the
    .FitToPagesTall = 1 
End With 
 
Set ActiveSheet.VPageBreaks(1).Location = ActiveSheet.Range("Q5") 'Aughhhh!!!!
Set ActiveSheet.VPageBreaks(2).Location = ActiveSheet.Range("AC5") 
Set ActiveSheet.VPageBreaks(3).Location = ActiveSheet.Range("AO5") 
 
 '...and so forth

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then I used the checkboxes to select all the appropriate sheets then print.


	VB:
	
 PrintOK_Click() 
    Run "printareas" 'Sets names ranges as printareas
     
    If PrintMenu.DevSum.Value = True Or PrintMenu.NOISum.Value = True Then 
         'Sheet8.Activate
        Sheet8.Select False 
    End If 
     
    If PrintMenu.DevBgt.Value = True Then 
         'Sheet3.Activate
        Sheet3.Select False 
    End If 
     
     'and so forth...

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.

My current muck ups are .....

1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?

2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=

3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.

I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.

To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.

I'm at a loss, or tired of fiddling with it. I don't expect someone to write it for me but if anyone wants to point me in a new direction, I'd appreciate it. FYI - I'm not a VBA guru.

Hi, all

I written a VBA code in one workbook , now i want to get some data from another workbooks by using refedit control to select some cells, but it seams this action can be not work!

Why ? if refedit is not support this action ? If so , what is the soultion to acheive this action ? can you help ? Thanks at advance.

I have the following code in Sheet1 which is where the range Priority exists.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("Priority"), Target) Is Nothing Then
Call FunctionOne
Else
Call FunctionTwo
End If
End Sub

If I am in a different Sheet and run the same code testing the named range
"Priority" I get this error message:

Method 'Range' of object'_worksheet' failed.

Is there a way to make it so I can test the Range from any Sheet in the file
so this code will work regardless of the sheet I am running it in. Note: the
range name I am testing will definitely exist. It just may not exist in the
current active sheet.

Thank you for your help.

Steven

I am trying to use a macro to compare data from 2 sheets and spit out matching data in a third sheet. The first sheet has two columns of data. the second sheet has 4 columns of data. Only one column from sheet 1 and 1 column from sheet 2 will actually have matching data.

All other columns in sheet 1 and 2 are associated to the matching columns and must "tag along" into the 3rd sheet. I would love the third sheet to have a button that I just click and it populates the data.

The macro will be comparing between 50,000 and 500,000 items. Possibly a good book to use or even a referral for a proffesioanl or even some good heartedness, would be greatly appreciated.

Ive included an attachemnt just in case. the headers will kind of give a clue.

Hi all!

Anyone who might know about this, please help.
I am using XL's Data Validation. I want to insert list of data from my other sheet's name range. XL only allow source to insert list from the current sheet only.

Is it possible to insert my list of data (which is in named range) from other sheet to insert as my source for data validation?

Thank you in advance!
=)

My problem is that i want to choose the data from other sheet, but every 7 days for example:

I just want to copy every 7 days, in this case it would be the 7,14,21,28 OF JAN

So in the next sheet i could drown a formula and search the info li this

7
14
21
28
etc

------A---------------B
01-janv-05
02-janv-05
03-janv-05
04-janv-05
05-janv-05
06-janv-05
07-janv-05 30
08-janv-05 32
09-janv-05 31
10-janv-05 29
11-janv-05 x
12-janv-05 x
13-janv-05 x
14-janv-05 29.5
15-janv-05 x
16-janv-05 x
17-janv-05 x
18-janv-05
19-janv-05 x
20-janv-05 x
21-janv-05 32
22-janv-05 x
23-janv-05
24-janv-05
25-janv-05
26-janv-05
27-janv-05 x
28-janv-05 31

Thanks for helping....

The following procedure works perfectly when the commented line is used
but when I try to use the Inputbox I get an error: "Ojbect required."
It turns out that the list may not start at the same place everytime
and there may be blanks or gaps. I tried to learn about the Inputbox
Method from the sample code at the bottom, which is from a book, but I
can't get it to work either. Any help would be appreciated.

Public Sub RunReports()
Dim vManagers As Range, vManager As Range, DataArray(5)
Windows("DataTable.xls").Activate
'Set vManagers = Range(Cells(23, "D"), Cells(Rows.Count,
"D").End(xlUp))
Set vManagers = Application.InputBox("Use the mouse to select all
manager names in Column D", "Select Range", , , , , , 8)
On Error Resume Next
For Each vManager In vManagers
If vManager.Value = "S. O' Neil" Then
vManager.Activate
DataArray(1) = ActiveCell.Offset(0, -3)
DataArray(2) = ActiveCell.Offset(0, -1)
DataArray(3) = ActiveCell.Offset(0, 1)
DataArray(4) = ActiveCell.Offset(0, 3)
DataArray(5) = ActiveCell.Offset(0, 7)
Windows("Check Deposit Report.xls").Activate
Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1)
ActiveSheet.Unprotect
Range("F43").Value = DataArray(1)
Range("B43").Value = DataArray(2)
Range("F32").Value = DataArray(3)
Range("A43").Value = DataArray(4)
Range("I27").Value = -DataArray(5)
Windows("DataTable.xls").Activate
End If
Next
Windows("Check Deposit Report.xls").Activate
End Sub

Sub GetRange()
Dim UserRange As Range
Default = Selection.Address 'Assignment to constant not permitted.
On Error GoTo Canceled
Set UserRange = Application.InputBox(prompt:="Range to erase:",
Title:="Range Erase", Default:=Default, Type:=8)
UserRange.Clear
UserRange.Select
Canceled:
End Sub

The following procedure works perfectly when the commented line is used
but when I try to use the Inputbox I get an error: "Ojbect required."
It turns out that the list may not start at the same place everytime
and there may be blanks or gaps. I tried to learn about the Inputbox
Method from the sample code at the bottom, which is from a book, but I
can't get it to work either. Any help would be appreciated.

Public Sub RunReports()
Dim vManagers As Range, vManager As Range, DataArray(5)
Windows("DataTable.xls").Activate
'Set vManagers = Range(Cells(23, "D"), Cells(Rows.Count,
"D").End(xlUp))
Set vManagers = Application.InputBox("Use the mouse to select all
manager names in Column D", "Select Range", , , , , , 8)
On Error Resume Next
For Each vManager In vManagers
If vManager.Value = "S. O' Neil" Then
vManager.Activate
DataArray(1) = ActiveCell.Offset(0, -3)
DataArray(2) = ActiveCell.Offset(0, -1)
DataArray(3) = ActiveCell.Offset(0, 1)
DataArray(4) = ActiveCell.Offset(0, 3)
DataArray(5) = ActiveCell.Offset(0, 7)
Windows("Check Deposit Report.xls").Activate
Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1)
ActiveSheet.Unprotect
Range("F43").Value = DataArray(1)
Range("B43").Value = DataArray(2)
Range("F32").Value = DataArray(3)
Range("A43").Value = DataArray(4)
Range("I27").Value = -DataArray(5)
Windows("DataTable.xls").Activate
End If
Next
Windows("Check Deposit Report.xls").Activate
End Sub

Sub GetRange()
Dim UserRange As Range
Default = Selection.Address 'Assignment to constant not permitted.
On Error GoTo Canceled
Set UserRange = Application.InputBox(prompt:="Range to erase:",
Title:="Range Erase", Default:=Default, Type:=8)
UserRange.Clear
UserRange.Select
Canceled:
End Sub

Hi,
In my macro i have a Data which start from cell A5. The number of columns in this data will change weekly. When i run the macro i want to the data to run the the titl of the sheet whic is is Row1 to merge across all the used columns. So i one week it may have to merge from A1:A10, the next week A1:A15.
i frequestly use a last row formula to select a range going down: for example Range("a1:&"a"&LastRow) and use LastRow = ActiveSheet.UsedRange.Rows.Count to set the last row then tried to adapt this for columns but i cant get it work.

Thanks in advance
Blunder1

Hello everyone:

My Question today is this: I have been using the Input method Technique to select any
range on a worksheet, for ex: range(A1:B3) , No Problem,The active cell becomes cell (A1). To Move down one cell from the active cell I use the offset function. Great now active cell becomes cell(A2) and then the offset function one more time brings the Active Cell to cell (A3). Now my problem is how do I get the active cell back up to cell (B1)? Would I use the offset function to move active cell from (A3) to (B1) or is there another easier way to do this?

The offset function I was using to move from cell A1:A3 is this:
activecell.offset(1,0).activate . Is there a way of using the offset function to bring it
back up from cell(A3) to Cell(B1)?

Please let me know

Thank you for listening to me

I received some much appreciated help with 2 small bits of code that I cant seem to get to work together in the same macro but that work great seperated. I need them to be in the same macro. The first just simply copy's text from one workbook to another:


	VB:
	
 Test2() 
    ActiveSheet. Range("a1").Copy _ 
    Destination:=Workbooks("punchlist.xls"). Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1,0) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
That worked ok but I needed to change it to the "active cell" instead of cell "a1". So then this line of code was made:


	VB:
	
 Range 
 
On  Error Resume Next 
Set userInputCell = Application.InputBox("Use the mouse to select a cell on any sheet", type:=8) 
On Error Goto 0 
 
If userInputCell Is Nothing Then 
    MsgBox "Cancel pressed" 
Else 
    Msgbox "You selected " & userInputCell.Address(,,,True) 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second code works just the way I want it but it doesnt copy over the text to the other workbook. I assume the 2 codes need to be together but I cant get it to work without errors. I also need the text to copy over without changing the borders on either workbook. Can this be done?
thanks for everyones help.

Hi Experts,

I'm using the following code.It copies the entire data from A to H.

Set dest =
Worksheets("FTA Metrics")
dest.Range("A2", dest.Range("A" & Rows.Count).End(xlUp).Offset(1,
0)).Resize(columnsize:=11).ClearContents
    Set Source = Worksheets("Master Sheet")
    Source.Range("A:A", "H:H").AutoFilter 8, "<FIRST TIME ACCEPTED>"
    Source.Range("A2", Source.Range("N" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
dest.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Source.AutoFilterMode = False
But, all I need is selected ranges only A,C & D only,
The modified the code as below (edited the 4th line of the code)


I'm getting error. Please tell me how to work with selected ranges.

I have some code to copy a range from one sheet to another... this working code is in a userform...
I copied this code to the sheet and used a button to call it, but I now get a 400 error.

Here's the working code from the userform;

	VB:
	
 CommandButton5_Click() 
     'Refresh item number columns
     
    Unload UserForm1 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    Sheets("N").Activate 
    Range("A12:B2000").Copy (Sheets("Floors").Range("A12")) 
    Sheets("Floors").Activate 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And this is what I have in the sheet;

	VB:
	
 RefreshN() 
     'Refresh item number columns
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    Sheets("N").Activate 
    Range("A12:B2000").Copy (Sheets("Floors").Range("A12")) 
    Sheets("Floors").Activate 
     
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've commented out all lines and brought them back one at a time until I get the error, which is with the range to be copied...
I changed copy to select just to confirm this.

Why is this code working from a userform, but not from a sheet?

Once again, your wisdom is appreciated.

I have a column (A1:A10) of values formatted as currency with a £ symbol.

£3.00
£4.00
£5.00
£6.00
£7.00
£1.00
£9.00
£11.00
£112.00
£12.00

I want the cell with the minimum value to be set to bold. The Application.Min method always finds the correct cell, I check this with a MsgBox output but when I search for this value using the .Find method it doesn't find it.

I tried changing the parameter: LookAt:=xlWhole to LookAt:=xlPart. When I do this it does find the cell but of course if I put a similar value in the preceding cell which also contains a matching part of the number I'm really looking for, it finds that instead.

I have some very simple code to do this attached to a button as follows:


	VB:
	
 CommandButton1_Click() 
    Dim rng       As Range 
    Dim minVal   As Variant 
    Dim target   As Variant 
     
    Set rng = Sheets("Sheet1").Range("A1:A10") 
     
    minVal = Application.Min(rng) 
    MsgBox minVal 
     
    Set target = Sheets("Sheet1").Range("A1:A10").Find(What:=minVal, LookIn:=xlValues, LookAt:=xlWhole) 
    If Not target Is Nothing Then 
        target.Font.Bold = True 
    Else 
        MsgBox "target not Found" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can I use the .Find method to select this cell?

Thank you

I am using the following code to import record from another sheet so the
record can be modified. The user has to look at the "Records" sheet, find the
row number of the record they want to modify, move to the AddRecord sheet and
input the row number in cell "A41" and click on the ImportRecordBtn. I want
to have the user click on a record on the "Records" sheet and then click on
ImportRecordBtn on the same sheet and have the record move to the AddRecord
sheet.

Sub ImportRecordBtn()

'Import data on AddRecord Sheet based on data selected from Records Sheet
ActiveWorkbook.Unprotect Password:="xxx"
Sheets("Records").Unprotect Password:="xxx"
Dim LName As String
Dim LType As String
Dim LAffectedSystem As String
Dim LNote As String
Dim LHours As Integer
Dim LModule As String
Dim LSubAssy As String
Dim LComponent As String
Dim LWaferCount As String
Dim LToolType As String
Dim LSerNo As Integer
Dim LResolved As String
Dim LKeyword As String
Dim LDept As String
Dim LOwner As String
Dim LFTEs As Integer
Dim LRecordNo As String

Dim LRow As Long
Dim LFound As Boolean

LRow = Range(A41).Address

' Move to Record sheet and record values based on row selected.
'Sheets("Records").Activate
'Sheets("Records").Unprotect Password:="xxx"

LName = Range("C" & LRow).Value
LNote = Range("D" & LRow).Value
LOwner = Range("E" & LRow).Value
LDept = Range("F" & LRow).Value
LType = Range("H" & LRow).Value
Range("I" & LRow).Value = "Yes"
Range("I" & LRow).Font.ColorIndex = 3
LToolType = Range("J" & LRow).Value
LSerNo = Range("K" & LRow).Value
LAffected = Range("L" & LRow).Value
LModule = Range("M" & LRow).Value
LSubAssy = Range("N" & LRow).Value
LComponent = Range("O" & LRow).Value
LHours = Range("P" & LRow).Value
LWaferCount = Range("Q" & LRow).Value
LKeyword = Range("R" & LRow).Value
LFTEs = Range("T" & LRow).Value
LRecordNo = Range("U" & LRow).Value

Sheets("Records").Protect Password:="xxx"

' Move to AddRecords sheet and input data based on Records sheet and Row
selected
Sheets("AddRecords").Visible = True
Sheets("Records").Visible = False
Sheets("AddRecords").Activate
Sheets("AddRecords").Unprotect Password:="xxx"

Range("B4").Value = LName
Range("C4").Value = LToolType
Range("D4").Value = LSerNo
Range("E4").Value = LWaferCount
Range("A8").Value = LType
Range("B8").Value = LAffected
Range("C8").Value = LModule
Range("D8").Value = LSubAssy
Range("E8").Value = LComponent
Range("A12").Value = LKeyword
Range("B12").Value = LNote
Range("E12").Value = LHours
Range("F12").Value = LFTEs
Range("G12").Value = "No"
Range("H5").Value = LDept
Range("I5").Value = LOwner
Range("A19").Value = LRecordNo

'Clear entries from cells
Range("E12").Value = ""

Sheets("AddRecords").Protect Password:="xxx"
ActiveWorkbook.Protect Password:="xxx"

End Sub

XL2002: How to Select Ranges Using Visual Basic for Applications (Novice Examples)

,I had just like to know what more ways are there to select range , here are some I have learned from this wonderful forum only . If this is spam ,mods pls delete this.

'Selecting till the end in one line

Range("a1" , range("a1").end(xldown)).select
'define a variable and do this
lastrow = range("a1").end(xldown).row

range("a1:a" &lastrow).select
'When using autofilter , you want to select everything that's visible on screen except the headers

ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select

I have been reading about select the last row , and then going up , which I haven't grasped yet.

I need the VBA string that will do the following:

Find the last used cell in Column M
Go to the Cell in Column A of that row
Select Range from that cell to cell in Column X

Example:
If the last used cell in column M is "M136"
Range("A136:X136").Select

Thanks In Advance,

JBMERREL

Hi all,

I am having a "run time error 424 object required" when I try to read rows from another sheet. My code works fine until the rows are selected. Then, I try to process every selected row, but I have this strange error... My code is this:

	VB:
	
 Range 
 
Worksheets("Sheet1").Activate 
 
Set sales = Worksheets("Sheet1").Range("table1") 
sales.AutoFilter Field:=25, Criteria1:="2011" 
sales.AutoFilter Field:=4, Criteria1:="sale" 
sales.SpecialCells(xlCellTypeVisible).Select 
 
If selection.Areas.Count

I have been brousing this site for information. Wonderful site! I figured out how to use a combobox to select which query to run but need to use a second combobox for the selection. 1st combobox would select a product to sort which would open a 2nd combo box to select a metric to sort on. My sheet has headings as follows:
PRODUCT 1_______________PRODUCT2______________PRODUCT3
SOM TREND_______________SOM TREND ____________SOM TREND
There are up to 8 products to choose from and usualy 4 metrics.
Here is the code I am using to select a macro from one combo box.

	VB:
	
 SortSelect() 
     
    Select Case SortSelect.Value 'Combobox
    Case Is = "Portfolio" 
        SortPortfolio 
    Case Is = "Product1" 
        SortProduct1 
    Case Is = "Product2" 
        SortProduct2 
    Case Is = "Product3" 
        SortProduct3 
         
    End Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines