Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again
--
Brian McCaffery


"Joel" wrote:

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

I can't answer the question

"Brian" wrote:

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again
--
Brian McCaffery


"Joel" wrote:

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

Here is a guess at what you are trying to do:

It assumes the formula in the cells of refrange are like

=Sheet3!B9
or
=Sheet3!$B$9

Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
s = Replace(c.formula,"=","")
set rng = [s]
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
Next c
End Select
End Sub

--
Regards,
Tom Ogilvy


"Brian" wrote:

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again
--
Brian McCaffery


"Joel" wrote:

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

Tom, thanks for that. But...

When I run it, I get the message box "Runtime error '424'
In Debug, if I hover over the "set rng" it says "rng=Nothing" If I then
hover over the following [s] it says "GSOPs$A$22" which is the correct
reference for the first in the range.

If I select the definition, it jumps to the rng in the Dim statement.

Any thoughts while I search?

Thanks,
--
Brian McCaffery


"Tom Ogilvy" wrote:

Here is a guess at what you are trying to do:

It assumes the formula in the cells of refrange are like

=Sheet3!B9
or
=Sheet3!$B$9

Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
s = Replace(c.formula,"=","")
set rng = [s]
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
Next c
End Select
End Sub

--
Regards,
Tom Ogilvy


"Brian" wrote:

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again
--
Brian McCaffery


"Joel" wrote:

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery

My error,
change that line to

set rng = Evaluate(s)

--
Regards,
Tom Ogilvy


"Brian" wrote:

Tom, thanks for that. But...

When I run it, I get the message box "Runtime error '424'
In Debug, if I hover over the "set rng" it says "rng=Nothing" If I then
hover over the following [s] it says "GSOPs$A$22" which is the correct
reference for the first in the range.

If I select the definition, it jumps to the rng in the Dim statement.

Any thoughts while I search?

Thanks,
--
Brian McCaffery


"Tom Ogilvy" wrote:

Here is a guess at what you are trying to do:

It assumes the formula in the cells of refrange are like

=Sheet3!B9
or
=Sheet3!$B$9

Private Sub ComboBox1_Change()
Dim i as Long, rng as Range
Dim refrange As Range
Dim c As Range

ComboBox2.ListIndex = -1
ComboBox3.ListIndex = -1
ComboBox4.ListIndex = -1
ComboBox5.ListIndex = -1

Select Case ComboBox1.Value
Case "GSOP_0286"

Set refrange = Sheets("Sheet2").Range("A3:A20")
i = 0
For Each c In refrange
s = Replace(c.formula,"=","")
set rng = [s]
rng.entireRow.copy
Sheets("Report").Range("A2") _
.offset(i,0) _
.PasteSpecial Paste:= _
xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
i = i + 1
Next c
End Select
End Sub

--
Regards,
Tom Ogilvy


"Brian" wrote:

Joel,

Thanks for the reply. It actually did something.

This is my first attempt at using Excel VBA. Wish I hadn't suggested putting
together a mini form of config management.

My error was not explaining everything. The cross references in each range
could be in any one of four sheets. When I click on the cell, I can see the
sheet and column\row reference in the formula bar. Is there a way to select
the sheet to activate after the Application.Double click. If I manually
double click on the cell it takes me to the correct location.

Thanks again
--
Brian McCaffery


"Joel" wrote:

I made some changes. Changed the functtion from click to change.

Private Sub ComboBox1_Change()


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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
'With ActiveSheet
Sheets("sheet2").Activate
myRow = ActiveCell.Row
Sheets("Sheet2").Rows(myRow).Select
Selection.Copy
Sheets("Report").Activate
Sheets("Report").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'End With
Next c
End Select
End Sub

"Brian" wrote:

Hello all,

I have a sheet containing a matrix of Cross references. Using the case
statement, I would like to be able to loop through each cell in the range,
double click to go to the sheet containing the data, select the row, then
paste into a report sheet. Guess what -I can't get it to work. Any
suggestions please.

This is one case statement from the code:

Private Sub ComboBox1_click()
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("Sheet2").Range("A3:A20")
For Each c In refrange
Application.DoubleClick
With ActiveSheet
myRow = ActiveCell.Row
Rows(myRow).Select
Selection.Copy
Sheets("Report").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
End With
Next c

Thanks,

--
Brian McCaffery