Application.GoTo.Reference query

[Guest]

Hi all (again)

Goalpost have changed.

The problem I have to overcome is, I have a database matrix for
X-references. I have added the cross-references as links to the first column,
specific row. Using the Case statement, I am trying to find each reference,
goto the source, select the row, then copy and paste into another worksheet.
Have tried various options, starting to get confused about the option tried.

Any guidance would be appreciated.


ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1
Dim refrange As Range
Dim c As Range
Select Case ComboBox1.Value
Case "GSOP_0286"
Set refrange = Sheets("Data").Range("A3:A20")
For Each c In refrange
ActiveCell.Select
Application.Goto Reference:=""
Rows.Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Next c

 

[Incidental]

Hi Brian

I'm not sure if this will be of any help to you but i thought it worth
a mention. what i do in a situation like yours is load all the
information into the combobox and hide all but the first column of
info then move the information using the combobox change event, this
means i don't have to write a ton of code for each case of a select
case statement. i have enclosed some example code below for you to
have a wee look over.

To test put some values in the range "A1:T23" then add a combobox to a
userform and paste the code below in the userform code module.

Option Explicit
Dim i As Integer
Dim NewRow As Range

Private Sub ComboBox1_Change()
'Activate the sheet you wish to move the data to
Worksheets("Report").Activate
'Set the "i" variable
i = 0
'Set "NewRow" variable to the next empty row in sheet
Set NewRow = [A65535].End(xlUp).Offset(1, 0)
'Set a loop
Do Until i = 20
'Put the value from the combobox to the cell
NewRow.Value = ComboBox1.List(ComboBox1.ListIndex, i)
'Iterate 1 cell to the right for the next pass
Set NewRow = NewRow.Offset(0, 1)
'Iterate "i" variable for the next pass
i = i + 1

Loop
'Set your active sheet to the data sheet
Worksheets("Data").Activate

End Sub

Private Sub UserForm_Initialize()
'Activate the sheet that holds your data
Worksheets("Data").Activate
'Load that data into your combobox
With ComboBox1
'Set the Number of columns you need
..ColumnCount = 20
'Pass the range of data to the combobox
..RowSource = "A1:T23"
'Hide all the columns other than the first one
..ColumnWidths = "100;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"

End With

End Sub

hope this helps you out

S