Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull data (sumif?) from an external workbook
I have a workbook "report". Col A has contains products. The number of
products in column A may vary but each product features only once. eg: TOTAL SALES FOR JANUARY ColA ColB ItemX 4 ItemY 5 ItemZ 4 Another workbook "sales" contains the individual sales of these items in one month. So Col A contains the products which may feature once or several times. Col B contains the Qty. eg: SALES FOR JANUARY ColA ColB ItemX 1 ItemX 3 ItemY 5 ItemZ 2 ItemZ 2 Both workbooks reside in the same folder. I want to write vba code which will populate the sum of sales for each product for the month into Col B of "report". I think a SumIf might be the way to go except for the following issues: "Sales" is an external workbook which is not open. The rows in each workbook can vary depending on the number of products or the number of sales. "Sales" may contain 5, 10 rows of data or even 100. "Report" may contain 5, 10 individual products or even 88. Can anyone suggest code which will pull data from the closed workbook "Sales" and populate ColB in "Report" Hope this makes sense. In case you cant tell I'm a beginner. Cheers Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull data (sumif?) from an external workbook
Try the below macro...Edit the path and Sheet name for Sales.xls
Sub MyMacro() Dim wb As Workbook, lngRow As Long Dim ws1 As Worksheet, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = ActiveSheet Set wb = Workbooks.Open("d:\Sales.xls") Set ws2 = wb.Sheets("Sheet1") For lngRow = 1 To ws1.Cells(Rows.Count, "A").End(xlUp).Row ws1.Range("B" & lngRow) = Application.SumIf(ws2.Range("A:A"), _ ws1.Range("A" & lngRow), ws2.Range("b:b")) Next wb.Close False Application.ScreenUpdating = True End Sub -- Jacob "Simon" wrote: I have a workbook "report". Col A has contains products. The number of products in column A may vary but each product features only once. eg: TOTAL SALES FOR JANUARY ColA ColB ItemX 4 ItemY 5 ItemZ 4 Another workbook "sales" contains the individual sales of these items in one month. So Col A contains the products which may feature once or several times. Col B contains the Qty. eg: SALES FOR JANUARY ColA ColB ItemX 1 ItemX 3 ItemY 5 ItemZ 2 ItemZ 2 Both workbooks reside in the same folder. I want to write vba code which will populate the sum of sales for each product for the month into Col B of "report". I think a SumIf might be the way to go except for the following issues: "Sales" is an external workbook which is not open. The rows in each workbook can vary depending on the number of products or the number of sales. "Sales" may contain 5, 10 rows of data or even 100. "Report" may contain 5, 10 individual products or even 88. Can anyone suggest code which will pull data from the closed workbook "Sales" and populate ColB in "Report" Hope this makes sense. In case you cant tell I'm a beginner. Cheers Simon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull data (sumif?) from an external workbook
Instead of SUMIF you can use SUMPRODUCT, which works on closed workbooks. With regards to the varying numbers of rows, you can use dynamic range names that grow and shrink with the data. To define a dynamic range name for all the numeric values in column A, use in your Sales file MyRange =$A$1:index($A:$A,match(99^99,$A:$A,1)) Create a similar range for your numbers MyNumberRange =$B$1:index($B:$B,match(99^99,$A:$A,1)) Note: Anchor the Match function on the same column, so the ranges are the same size. Then reference these ranges in a SUMPRODUCT formula in your Report workbook =Sumproduct(--([Sales.xls]'Sheet1'!MyRange=A1),[Sales.xls]'Sheet1'!MyNumberRange) regards, teylyn Simon;674892 Wrote: I have a workbook "report". Col A has contains products. The number of products in column A may vary but each product features only once. eg: TOTAL SALES FOR JANUARY ColA ColB ItemX 4 ItemY 5 ItemZ 4 Another workbook "sales" contains the individual sales of these items in one month. So Col A contains the products which may feature once or several times. Col B contains the Qty. eg: SALES FOR JANUARY ColA ColB ItemX 1 ItemX 3 ItemY 5 ItemZ 2 ItemZ 2 Both workbooks reside in the same folder. I want to write vba code which will populate the sum of sales for each product for the month into Col B of "report". I think a SumIf might be the way to go except for the following issues: "Sales" is an external workbook which is not open. The rows in each workbook can vary depending on the number of products or the number of sales. "Sales" may contain 5, 10 rows of data or even 100. "Report" may contain 5, 10 individual products or even 88. Can anyone suggest code which will pull data from the closed workbook "Sales" and populate ColB in "Report" Hope this makes sense. In case you cant tell I'm a beginner. Cheers Simon -- teylyn Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com) ------------------------------------------------------------------------ teylyn's Profile: 983 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188544 http://www.thecodecage.com/forumz/chat.php |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is the best way to pull in external data from SQL table | Excel Discussion (Misc queries) | |||
Pull parameter from worksheet for External Data Query to SQL Datab | Excel Discussion (Misc queries) | |||
UDF alternative to PULL that opens external workbook and grabs val | Excel Worksheet Functions | |||
External Data to be used as in a pull down menu in Excel2003 | Excel Discussion (Misc queries) | |||
How do I pull multiple queries of external data from the Internet into Excel? | Excel Programming |