pass data between workbooks
 

I am looking for a sort of drill down reporting feature.

I have two workbooks. WB1 and Wb2.
Wb1 has cells with red or green color based on some criteria.

When clikced on any particular red cell, it should open Wb2 and show only
that information for that particular cell value.

How we can pass the clicked cell value to the other workbook, based on which
I can filter the data in Wb2 and show only relevant data. If we could some
how pass some range of values in the clicked cell row in Wb1 to Wb2, it would
be more better.

Thanks in Advance.

pass data between workbooks
 

"VishalBade" schreef in bericht
...
I am looking for a sort of drill down reporting feature.

I have two workbooks. WB1 and Wb2.
Wb1 has cells with red or green color based on some criteria.

When clikced on any particular red cell, it should open Wb2 and show only
that information for that particular cell value.

How we can pass the clicked cell value to the other workbook, based on
which
I can filter the data in Wb2 and show only relevant data. If we could some
how pass some range of values in the clicked cell row in Wb1 to Wb2, it
would
be more better.

Thanks in Advance.


'CommandButton on the worksheet
Private Sub Worksheet_Button_CopyRange_Click()

'Objects
'- 2 Workbooks, 1 to copy from, 1 to paste to
'- 2 Worksheets, 1 to copy from, 1 to paste to
'- 1 Range, to copy and paste
Dim wb1, wb2 As Workbook
Dim ws1, ws2 As Worksheet
Dim r As Range

'Current workbook and open a second workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("Workbook2.xls")

'Sheet1 & Sheet1 in both workbooks
Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet1")

'Activate Sheet1 in 1st Workbook,
'Select the range B5:K5 and
'copy it.
ws1.Activate
Set r = ws1.Range("B5:K5")
r.Copy

'Activate Sheet1 in 2nd Workbook,
'Select cell B5 and paste
ws2.Activate
ws2.Cells(5, 2).Select
ActiveSheet.Paste

'Release object variables
Set r = Nothing
Set ws2 = Nothing
Set ws1 = Nothing
Set wb2 = Nothing
Set wb1 = Nothing

End Sub



Moon

pass data between workbooks
 

Thanks very much moon for your time.

In my wb1 I have around 40 rows with red and green colored cells.
Instead of hardcoding the range to copy from wb1 (B5:K5 ), is there a way of
knowing which row number(red cell) was clicked in wb1.

Thanks again Vishal bade

"moon" wrote:



"VishalBade" schreef in bericht
...
I am looking for a sort of drill down reporting feature.

I have two workbooks. WB1 and Wb2.
Wb1 has cells with red or green color based on some criteria.

When clikced on any particular red cell, it should open Wb2 and show only
that information for that particular cell value.

How we can pass the clicked cell value to the other workbook, based on
which
I can filter the data in Wb2 and show only relevant data. If we could some
how pass some range of values in the clicked cell row in Wb1 to Wb2, it
would
be more better.

Thanks in Advance.


'CommandButton on the worksheet
Private Sub Worksheet_Button_CopyRange_Click()

'Objects
'- 2 Workbooks, 1 to copy from, 1 to paste to
'- 2 Worksheets, 1 to copy from, 1 to paste to
'- 1 Range, to copy and paste
Dim wb1, wb2 As Workbook
Dim ws1, ws2 As Worksheet
Dim r As Range

'Current workbook and open a second workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("Workbook2.xls")

'Sheet1 & Sheet1 in both workbooks
Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet1")

'Activate Sheet1 in 1st Workbook,
'Select the range B5:K5 and
'copy it.
ws1.Activate
Set r = ws1.Range("B5:K5")
r.Copy

'Activate Sheet1 in 2nd Workbook,
'Select cell B5 and paste
ws2.Activate
ws2.Cells(5, 2).Select
ActiveSheet.Paste

'Release object variables
Set r = Nothing
Set ws2 = Nothing
Set ws1 = Nothing
Set wb2 = Nothing
Set wb1 = Nothing

End Sub



Moon