Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying rows to all possible records during a macro
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 (perhaps 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". childofthe1980s |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying rows to all possible records during a macro
When it comes to dynamic ranges like this I usually get a count of the number
of rows and then paste the formula down to that row... for example Dim x as Integer x = Range("A1").End(xlDown).Row Range("B1").Formula = "Yourformula" Range("B1").Copy Range("B2:B" & x).PasteSpecial "childofthe1980s" wrote: 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 (perhaps 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". childofthe1980s |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying rows to all possible records during a macro
Yeah, but the number of rows could be any amount. Is there a way to
dynamically program this into VBA? I already pasted the formulas down to 3231 rows. What if, next time, it's 3232 rows? childofthe1980s "akphidelt" wrote: When it comes to dynamic ranges like this I usually get a count of the number of rows and then paste the formula down to that row... for example Dim x as Integer x = Range("A1").End(xlDown).Row Range("B1").Formula = "Yourformula" Range("B1").Copy Range("B2:B" & x).PasteSpecial "childofthe1980s" wrote: 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 (perhaps 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". childofthe1980s |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying rows to all possible records during a macro
Every time you run the macro
Range("A1").End(xlDown).Row will regenerate So no matter how many rows you have it will start from A1 and count all the way til it reaches a blank cell. So it would matter if you had 5 or 50000. The Range("B2:B" & x) Basically is the same thing as something like Range("B2:B3231") Except x is a variable and dependent on how many rows of data there are. "childofthe1980s" wrote: Yeah, but the number of rows could be any amount. Is there a way to dynamically program this into VBA? I already pasted the formulas down to 3231 rows. What if, next time, it's 3232 rows? childofthe1980s "akphidelt" wrote: When it comes to dynamic ranges like this I usually get a count of the number of rows and then paste the formula down to that row... for example Dim x as Integer x = Range("A1").End(xlDown).Row Range("B1").Formula = "Yourformula" Range("B1").Copy Range("B2:B" & x).PasteSpecial "childofthe1980s" wrote: 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 (perhaps 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". childofthe1980s |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying rows to all possible records during a macro
Thank you!!!!
childofthe1980s "akphidelt" wrote: Every time you run the macro Range("A1").End(xlDown).Row will regenerate So no matter how many rows you have it will start from A1 and count all the way til it reaches a blank cell. So it would matter if you had 5 or 50000. The Range("B2:B" & x) Basically is the same thing as something like Range("B2:B3231") Except x is a variable and dependent on how many rows of data there are. "childofthe1980s" wrote: Yeah, but the number of rows could be any amount. Is there a way to dynamically program this into VBA? I already pasted the formulas down to 3231 rows. What if, next time, it's 3232 rows? childofthe1980s "akphidelt" wrote: When it comes to dynamic ranges like this I usually get a count of the number of rows and then paste the formula down to that row... for example Dim x as Integer x = Range("A1").End(xlDown).Row Range("B1").Formula = "Yourformula" Range("B1").Copy Range("B2:B" & x).PasteSpecial "childofthe1980s" wrote: 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 (perhaps 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". childofthe1980s |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for copying a value to a variable no. of rows | Excel Discussion (Misc queries) | |||
Copying and Pasting Rows Macro | Excel Discussion (Misc queries) | |||
Macro to keep 15 specific records and delete 1000 other rows. | Excel Programming | |||
copying rows using macro | Excel Programming | |||
Help Please - VBA Macro- Copying rows to new file | Excel Programming |