Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro: Copying to all possible rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 122
Default Macro: Copying to all possible rows
Hello:

Could someone please review my code and the thread below and give me some
options? The thread is on the second or third page of this message baord
already, and I fear that it is getting ignored.

Hi:

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:=xlDatab ase, 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").AddDataFiel d
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

"someone" wrote:

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 someone
"childofthe1980s" wrote in
message ...
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Macro: Copying to all possible rows
I used data in column A to determine the last row. I also cleaned up the code.

Sub Consolidated()
'
' Consolidated Macro
' Macro recorded 4/21/2008 by John Ellis
'
'
With ActiveSheet
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R1864C12").CreatePivotTab le _
TableDestination:="", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
.PivotTableWizard TableDestination:=.Range("A3")
With .PivotTables("PivotTable1").PivotFields("Item Number")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable1").AddDataField
.PivotTables("PivotTable1").PivotFields _
("Qty On Hand"), _
"Sum of Qty On Hand", _
xlSum

.PivotTables("PivotTable1"). _
PivotFields("Sum of Qty On Hand").Function = xlAverage
End With
With Sheets("Sheet1")
.Columns("A:B").Delete
.Columns("F:I").Delete
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("G1") = "Past Due"
.Range("H1") = "Due This Week"
.Range("I1") = "Due in the Future"
.Columns("H:H").EntireColumn.AutoFit
.Columns("I:I").EntireColumn.AutoFit
.Range("G" & LastRow).FormulaR1C1 = "=IF(RC[-1]<TODAY(), RC[-3], 0)"
.Range("H" & LastRow).FormulaR1C1 = _
"=IF(AND(RC[-2]TODAY(), RC[-2]<TODAY()+7), RC[-4], 0)"
.Range("I" & LastRow).FormulaR1C1 = "=IF(RC[-3]TODAY(), RC[-5], 0)"
.Range("G2:I2").Copy _
Destination:=.Range("G3")
.Range("A1").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(7, 8, 9), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End With
End Sub



"childofthe1980s" wrote:

Hello:

Could someone please review my code and the thread below and give me some
options? The thread is on the second or third page of this message baord
already, and I fear that it is getting ignored.

Hi:

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:=xlDatab ase, 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").AddDataFiel d
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

"someone" wrote:

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 someone
"childofthe1980s" wrote in
message ...
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 122
Default Macro: Copying to all possible rows
Thanks so much, Joel! This is great!

childofthe1980s

"Joel" wrote:

I used data in column A to determine the last row. I also cleaned up the code.

Sub Consolidated()
'
' Consolidated Macro
' Macro recorded 4/21/2008 by John Ellis
'
'
With ActiveSheet
ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R1864C12").CreatePivotTab le _
TableDestination:="", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
.PivotTableWizard TableDestination:=.Range("A3")
With .PivotTables("PivotTable1").PivotFields("Item Number")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable1").AddDataField
.PivotTables("PivotTable1").PivotFields _
("Qty On Hand"), _
"Sum of Qty On Hand", _
xlSum

.PivotTables("PivotTable1"). _
PivotFields("Sum of Qty On Hand").Function = xlAverage
End With
With Sheets("Sheet1")
.Columns("A:B").Delete
.Columns("F:I").Delete
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("G1") = "Past Due"
.Range("H1") = "Due This Week"
.Range("I1") = "Due in the Future"
.Columns("H:H").EntireColumn.AutoFit
.Columns("I:I").EntireColumn.AutoFit
.Range("G" & LastRow).FormulaR1C1 = "=IF(RC[-1]<TODAY(), RC[-3], 0)"
.Range("H" & LastRow).FormulaR1C1 = _
"=IF(AND(RC[-2]TODAY(), RC[-2]<TODAY()+7), RC[-4], 0)"
.Range("I" & LastRow).FormulaR1C1 = "=IF(RC[-3]TODAY(), RC[-5], 0)"
.Range("G2:I2").Copy _
Destination:=.Range("G3")
.Range("A1").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(7, 8, 9), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
End With
End Sub



"childofthe1980s" wrote:

Hello:

Could someone please review my code and the thread below and give me some
options? The thread is on the second or third page of this message baord
already, and I fear that it is getting ignored.

Hi:

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:=xlDatab ase, 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").AddDataFiel d
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

"someone" wrote:

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 someone
"childofthe1980s" wrote in
message ...
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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
macro: Copying to all possible rows childofthe1980s Excel Programming 5 April 23rd 08 10:27 PM
copying rows to all possible records during a macro childofthe1980s Excel Programming 4 April 22nd 08 02:35 AM
Macro for copying a value to a variable no. of rows Ian Grega Excel Discussion (Misc queries) 6 April 15th 08 02:48 PM
copying rows using macro ashishprem Excel Programming 2 February 13th 06 01:02 PM
Help Please - VBA Macro- Copying rows to new file Uziel Excel Programming 2 September 13th 04 01:36 PM


All times are GMT +1. The time now is 07:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement