macro: Copying to all possible rows

C

childofthe1980s

Hello:

I have created a macro that, among other things, copies a formula from the
first row of records to the remaining rows of records in the spreadsheet of
data that I exported to Excel from an accounting application.

Now, is there a way in VBA Editor that I can tell the macro to copy this
formula to any and all possible records that are exported to Excel? I can
see where, if there are more or less records exported during the next export,
that some of the programming can be "lost" and either too little or too much
pasting of rows can be done.

Here is the range "line item" in VBA Editor that I need to edit:
Range("G3:I1864").Select

I need to, in essence, take out the I1864 and put in whatever the possible
last cell could be. That way the macro will select (before pasting) the
complete possible range of records.

I don't want to use the last cell in Excel, as that would force the clinet
to have to hunt to the bottom of the world to find the last record. I just
want to have the macro copy to the last possible record.

I posted this question yesterday on the message board. But, the solution
given to me by someone else frankly did not work. It gave me debugging
errors. And, with the line item that I just gave you in VBA Editor, there
was not a way to take his code and "marry" it to mine.

Please help!

childofthe1980s
 
C

childofthe1980s

No, this did not work either. It worked for maybe the first two records of
data, but "0" showed in the remaining thousands of records. It was as if the
formulas were not copied down.

Are there any other methods?

childofthe1980s
 
O

Otto Moehrbach

The snippet of code I gave you only defines and selects the range from G3 to
the last entry in Column I, and it will do that every time. How are you
using that line of code? Post your code and explain what it does and what
it is supposed to do. HTH Otto
 
C

childofthe1980s

Hi Otto:

My code is below. As far as what it accomplishes, please review toward the
end. I am copying three sets of formulas into three columns (one formula per
column).

Thanks, for looking at this.

Sub Consolidated()
'
' Consolidated Macro
' Macro recorded 4/21/2008 by John Ellis
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1864C12").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item Number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Qty On Hand"), "Sum of Qty On Hand", xlSum
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Qty On
Hand"). _
Function = xlAverage
Sheets("Sheet1").Select
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("F:I").Select
Selection.Delete Shift:=xlToLeft
Range("G1").Select
ActiveCell.FormulaR1C1 = "Past Due"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Due This Week"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Due in the Future"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<TODAY(), RC[-3], 0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]>TODAY(), RC[-2]<TODAY()+7), RC[-4], 0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TODAY(), RC[-5], 0)"
Range("G2:I2").Select
Selection.Copy
Range("G3:I1864").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8,
9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

childofthe1980s
 
O

Otto Moehrbach

The only part of your code that I recognize as being a part of what you have
been asking about is:
Range("G2:I2").Select
Selection.Copy
Range("G3:I1864").Select
ActiveSheet.Paste
I see where you place a formula in G2, H2 and I2. Is your code working up
to that point? IOW do you get the formulas you want in those 3 cells?
Then you copy those 3 cells and I take it that you want to paste those
formulas into G3:I1864. Just say:
Range("G3:G1864").PasteSpecial
But from earlier questions you had I understand that "1864" is a variable
row and you instead want to find the last row. Use this:
Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).PasteSpecial
I cleaned up some of your code. The macro recorder records every move you
make and all those moves are not necessary. That code is below and includes
the above suggested code:
Note that I used Column G to find the last row. That may not be appropriate
for your data. That's your call. If you want the code to look at some
other column to find the last row, substitute the proper column letter(s) in
this line:
Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).PasteSpecial
For instance, if you want to use Column D, then change the line to:
Range("G3:G" & Range("D" & Rows.Count).End(xlUp).Row).PasteSpecial
Don't change the "G" in "G3:G".
HTH Otto

Sub Consolidated()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1864C12").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item Number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields ("Qty On Hand"), "Sum of Qty On Hand",
xlSum
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Qty OnHand").
_
Function = xlAverage
Sheets("Sheet1").Select
Columns("A:B").Delete Shift:=xlToLeft
Columns("F:I").Delete Shift:=xlToLeft
Range("G1").Value = "Past Due"
Range("H1").Value = "Due This Week"
Range("I1").Value = "Due in the Future"
Columns("H:I").EntireColumn.AutoFit
Range("G2").FormulaR1C1 = "=IF(RC[-1]<TODAY(), RC[-3], 0)"
Range("H2").FormulaR1C1 = _
"=IF(AND(RC[-2]>TODAY(), RC[-2]<TODAY()+7), RC[-4], 0)"
Range("I2").FormulaR1C1 = "=IF(RC[-3]>TODAY(), RC[-5], 0)"
Range("G2:I2").Copy
Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).PasteSpecial
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8,
9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

childofthe1980s said:
Hi Otto:

My code is below. As far as what it accomplishes, please review toward
the
end. I am copying three sets of formulas into three columns (one formula
per
column).

Thanks, for looking at this.

Sub Consolidated()
'
' Consolidated Macro
' Macro recorded 4/21/2008 by John Ellis
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1864C12").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item Number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Qty On Hand"), "Sum of Qty On Hand",
xlSum
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Qty On
Hand"). _
Function = xlAverage
Sheets("Sheet1").Select
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("F:I").Select
Selection.Delete Shift:=xlToLeft
Range("G1").Select
ActiveCell.FormulaR1C1 = "Past Due"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Due This Week"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Due in the Future"
Columns("I:I").Select
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<TODAY(), RC[-3], 0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]>TODAY(), RC[-2]<TODAY()+7), RC[-4], 0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TODAY(), RC[-5], 0)"
Range("G2:I2").Select
Selection.Copy
Range("G3:I1864").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8,
9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

childofthe1980s

Otto Moehrbach said:
The snippet of code I gave you only defines and selects the range from G3
to
the last entry in Column I, and it will do that every time. How are you
using that line of code? Post your code and explain what it does and
what
it is supposed to do. HTH Otto
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copying rows to all possible records during a macro 4
Change a great number of words in word files, with a macro as short as possible. 5
Need Help with Macro Security 1
Macro warning in word 1
Setting column width in a table with a macro is not working 0
Menu Macro Not Executing - Need Help 1
Select Text to End of Footnote 9
How to clear prior search terms from Navigation Pane 0

Top