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

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

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

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

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