Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pass data from UserForm to spreadsheet | Excel Programming | |||
SOS-How to pass array parameter to Workbooks.OpenText(...) in VC++ | Excel Programming | |||
How to pass FieldInfo parameter to Workbooks::OpenText(...) in VC+ | Excel Programming | |||
How to pass value in cell between workbooks ? | Excel Programming | |||
How to pass value between workbooks ? | Excel Programming |