varying data range

stealthdevil · July 20th, 2007, 09:25 AM

Hi,

I have a workbook with 1 sheet that contains data and 7 sheets that have pivot tables building off of the data from the first sheet. This data gets imported in daily and varies in range. I use workbook.refreshall to run the fresh data through the pivot tables.

How can I set up the pivot tables to get all the data from the 1st sheet regardless of what the range is?

Thanks,
David

FullSquat · July 22nd, 2007, 07:58 AM

Use a dynamic range name for the pivot table data range. The general formula is;
=OFFSET(SheetName!$A:$1,0,0,COUNTA(SheetName!$A:$A ),COUNTA(SheetName!$1:$1))
A1 is the first cell of the data range.

stealthdevil · July 24th, 2007, 01:55 PM

Thanks FullSquat! that worked great.
Now, One more thing I'm having trouble with. I have a couple of formulas that go along with the data I'm bringing in. They are in columns beside the data. I need for the formulas to "drag down" with the rows of data...i.e. J3x4,J4x4,J5x4,J6x4.....The formula is the same it just changes w/ the row number. How can I do this?

thanks,
David

hudeanee · July 25th, 2007, 01:16 AM

I dont understand whats wrong.
i did f1*4 and i dragged it down and it workes fine.
= f2*4 f5*4 and so on...

stealthdevil · July 25th, 2007, 12:41 PM

I want it to be automated w/ vba, so when new data is imported in the formula it will drag down with the new data and stop at the last row of data.

stealthdevil · July 25th, 2007, 12:48 PM

I think I've almost got it working like I want.
I put this code in a module:

Code:

Function LastCell(ws As Worksheet) As Range
  Dim LastRow&, LastCol%
  On Error Resume Next

  With ws
    LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
    LastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
  End With
  Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

Then I call that function from the macro I created, like so:

Code:

Dim sLast As String
sLast = LastCell(Sheet1).Row

    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-16]=""0247"",""W"",LEFT(RC[ 18],1))"
    Selection.AutoFill Destination:=Range("X2:X" + sLast), Type:=xlFillDefault
    Range("X2:X" + sLast).Select
    ActiveWindow.ScrollRow = 2

The only thing now is that sLast is getting a value of 11 every time and I haven't been able to figure out why yet. There are about 8,000 rows of data, so 11 is a tad off.

FullSquat · July 27th, 2007, 02:30 AM

Here is a suggestion. Select a column with continuous data and use VBA code similar to the following:

    Dim WS As Worksheet
    Dim Rcell As Range

    Set WS = Sheets("SheetName")
    Set Rcell = WS.Range("A2") 'First cell of data

    Do Until IsEmpty(Rcell.Value) 'Stops at last cell in the column

        'Enter Formula in offset columns

        Rcell.Offset(0, 3).FormulaR1C1 = "=45*RC[-2]"

        'Moves Rcell to the next row

   Set Rcell = Rcell.Offset(1, 0)

    Loop

The program will enter the formula in the column offset to the right as specified in the Offset method, move down the column to the next row and stop at the last row in the column.