Prev: Self Signed Digital Certificates.
Next: Matching Data between 2 Tabs on Same Workbook - then calculating
From: Simon on 16 Mar 2010 22:02
Hi
I am using the macro below to pull some data from an external workbook.
The 2 issues I need to sort are:
1. The number of rows in the external workbook can vary. How do I amend this
code to pull all of the rows with data?
2. The number of rows in the autofill also may vary. How do I autofill only
the number of rows required? i.e the number of rows in column A that contain
data.

'Lookup Previous Month Sales
Columns("K:K").Select
Selection.NumberFormat = "General"
Range("K4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("K4:K300"), Type:=xlFillDefault
Range("K3").Select
ActiveCell.FormulaR1C1 = "Previous Month"

I am new to VBA so simple speak is appreciated.
Many thanks
Simon
From: OssieMac on 16 Mar 2010 23:19
Hi again Simon,

The following demonstrates the method of finding the last cell containing
data in a column. Explanation is that it is like placing the cursor on the
very last cell in a column and holding the Ctrl key and press the up arrow
and the cursor stops at the first cell with data.

You can then concatenate the variable in the range statement.

I have used Select in the example but there is generally no reason to select
cells to manipulate them. You simply address the range.

Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Range("A2:A" & lastRow).Select

--
Regards,

OssieMac


"Simon" wrote:

> Hi
> I am using the macro below to pull some data from an external workbook.
> The 2 issues I need to sort are:
> 1. The number of rows in the external workbook can vary. How do I amend this
> code to pull all of the rows with data?
> 2. The number of rows in the autofill also may vary. How do I autofill only
> the number of rows required? i.e the number of rows in column A that contain
> data.
>
> 'Lookup Previous Month Sales
> Columns("K:K").Select
> Selection.NumberFormat = "General"
> Range("K4").Select
> Selection.FormulaArray = _
> "=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
> Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
> Customer - Excel Ex'!R6C14:R263C14,0))"
> Selection.AutoFill Destination:=Range("K4:K300"), Type:=xlFillDefault
> Range("K3").Select
> ActiveCell.FormulaR1C1 = "Previous Month"
>
> I am new to VBA so simple speak is appreciated.
> Many thanks
> Simon
From: Simon on 17 Mar 2010 15:15
Thanks very much OssieMac.
That solves the autofill on the current sheet.
How do I also include this in the external workbook reference to ensure I
get all the rows.

Note that this worksheet pulls data from 12 different external workbooks
(all with varying amount of rows)so I have 12 different sumifs.

"OssieMac" wrote:

> Hi again Simon,
>
> The following demonstrates the method of finding the last cell containing
> data in a column. Explanation is that it is like placing the cursor on the
> very last cell in a column and holding the Ctrl key and press the up arrow
> and the cursor stops at the first cell with data.
>
> You can then concatenate the variable in the range statement.
>
> I have used Select in the example but there is generally no reason to select
> cells to manipulate them. You simply address the range.
>
> Dim lastRow As Long
> With ActiveSheet
> lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
> End With
>
> Range("A2:A" & lastRow).Select
>
> --
> Regards,
>
> OssieMac
>
>
> "Simon" wrote:
>
> > Hi
> > I am using the macro below to pull some data from an external workbook.
> > The 2 issues I need to sort are:
> > 1. The number of rows in the external workbook can vary. How do I amend this
> > code to pull all of the rows with data?
> > 2. The number of rows in the autofill also may vary. How do I autofill only
> > the number of rows required? i.e the number of rows in column A that contain
> > data.
> >
> > 'Lookup Previous Month Sales
> > Columns("K:K").Select
> > Selection.NumberFormat = "General"
> > Range("K4").Select
> > Selection.FormulaArray = _
> > "=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
> > Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
> > Customer - Excel Ex'!R6C14:R263C14,0))"
> > Selection.AutoFill Destination:=Range("K4:K300"), Type:=xlFillDefault
> > Range("K3").Select
> > ActiveCell.FormulaR1C1 = "Previous Month"
> >
> > I am new to VBA so simple speak is appreciated.
> > Many thanks
> > Simon
From: OssieMac on 17 Mar 2010 22:38
Hi Simon,

I need to see the code that you are using to get the data from the source
workbooks.

If you have not yet coded that part, then:

How do I identify the source workbooks? Are they all in the one folder?

Are the source workbooks the only workbooks in the source folder? If not then:

Do the source workbook names follow a pattern so that I can use wildcards to
identify and open each one in turn?

What is the Sheet name and column in the source workbooks where you want to
extract the data from.

What is the Sheet name and column in the destination workbook? (I am
assuming that you want the data in the destination workbook all in one column
one under the other. Is this correct?)

Where will the VBA code be located? (In the Destination workbook or in a
workbook of its own?) If in a workbook of its own, what is the name of the
destination workbook.

There may be more questions once I see what you return to me so continue to
monitor this thread as frequently as possible.

--
Regards,

OssieMac


From: Simon on 17 Mar 2010 23:50
Hi
Code is below. All source spreadsheets sit in C:\ABB
The Destination workbook is C:\ABB\ABBPipelineReport.xls, Worksheet is
"Report".
You can see the name of each workbook in the code below i.e
SalesCurrentMonth, SalesPreviousMonth,SalesPreviousMonth-1,
SalesPreviousMonth-2 etc
The vba is in the Destination workbook.
Their are other source workbooks in C:\ABB
You will also notice that for some reason the 11th and 12th formulas for the
11th and 12th month of Sales open the external workbook. this is because only
these 2 formulas return #REF! if I dont. The previous 10 work fine without
opening external workbooks.

Hopes this all makes sense, thanks again for your help.
Simon


'Lookup Current Month Sales
Range("J4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesCurrentMonth.xls]Category by Customer - Excel
Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesCurrentMonth.xls]Category by Customer -
Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("J4:J" & LR), Type:=xlFillDefault
Range("J3").Select
ActiveCell.FormulaR1C1 = "Current Month"


'Lookup Previous Month Sales
Range("K4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("K4:K" & LR), Type:=xlFillDefault
Range("K3").Select
ActiveCell.FormulaR1C1 = "Previous Month"

'Lookup Previous Month-1 Sales
Range("L4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-1.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-1.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("L4:L" & LR), Type:=xlFillDefault
Range("L3").Select
ActiveCell.FormulaR1C1 = "Previous Month-1"

'3 Month Rolling Average
Columns("I:I").Select
Selection.NumberFormat = "0"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[1]:RC[3])"
Range("I4").Select
Selection.AutoFill Destination:=Range("I4:I" & LR), Type:=xlFillDefault

'Lookup Previous Month-2 Sales
Range("M4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-2.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-2.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("M4:M" & LR), Type:=xlFillDefault
Range("M3").Select
ActiveCell.FormulaR1C1 = "Previous Month-2"

'Lookup Previous Month-3 Sales
Range("N4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-3.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-3.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("N4:N" & LR), Type:=xlFillDefault
Range("N3").Select
ActiveCell.FormulaR1C1 = "Previous Month-3"

'Lookup Previous Month-4 Sales
Range("O4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-4.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-4.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("O4:O" & LR), Type:=xlFillDefault
Range("O3").Select
ActiveCell.FormulaR1C1 = "Previous Month-4"

'Lookup Previous Month-5 Sales
Range("P4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-5.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-5.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("P4:P" & LR), Type:=xlFillDefault
Range("P3").Select
ActiveCell.FormulaR1C1 = "Previous Month-5"

'Lookup Previous Month-6 Sales
Range("Q4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-6.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-6.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("Q4:Q" & LR), Type:=xlFillDefault
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Previous Month-6"

'Lookup Previous Month-7 Sales
Range("R4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-7.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-7.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("R4:R" & LR), Type:=xlFillDefault
Range("R3").Select
ActiveCell.FormulaR1C1 = "Previous Month-7"

'Lookup Previous Month-8 Sales
Range("S4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-8.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-8.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("S4:S" & LR), Type:=xlFillDefault
Range("S3").Select
ActiveCell.FormulaR1C1 = "Previous Month-8"

'Lookup Previous Month-9 Sales
Range("T4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-9.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-9.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("T4:T" & LR), Type:=xlFillDefault
Range("T3").Select
ActiveCell.FormulaR1C1 = "Previous Month-9"
Application.ScreenUpdating = False
Workbooks.Open(Filename:="C:\ABB\SalesPreviousMonth-9.xls", Origin:= _
xlWindows).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Close False
Application.ScreenUpdating = True

'Lookup Previous Month-10 Sales
Range("U4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-10.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-10.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("U4:U" & LR), Type:=xlFillDefault
Range("U3").Select
ActiveCell.FormulaR1C1 = "Previous Month-10"
Application.ScreenUpdating = False
Workbooks.Open(Filename:="C:\ABB\SalesPreviousMonth-10.xls", Origin:= _
xlWindows).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Close False
Application.ScreenUpdating = True

"OssieMac" wrote:

> Hi Simon,
>
> I need to see the code that you are using to get the data from the source
> workbooks.
>
> If you have not yet coded that part, then:
>
> How do I identify the source workbooks? Are they all in the one folder?
>
> Are the source workbooks the only workbooks in the source folder? If not then:
>
> Do the source workbook names follow a pattern so that I can use wildcards to
> identify and open each one in turn?
>
> What is the Sheet name and column in the source workbooks where you want to
> extract the data from.
>
> What is the Sheet name and column in the destination workbook? (I am
> assuming that you want the data in the destination workbook all in one column
> one under the other. Is this correct?)
>
> Where will the VBA code be located? (In the Destination workbook or in a
> workbook of its own?) If in a workbook of its own, what is the name of the
> destination workbook.
>
> There may be more questions once I see what you return to me so continue to
> monitor this thread as frequently as possible.
>
> --
> Regards,
>
> OssieMac
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Self Signed Digital Certificates.
Next: Matching Data between 2 Tabs on Same Workbook - then calculating