How would I fill a several columns with the value in the first row

  • Thread starter Thread starter BruceJ
  • Start date Start date
B

BruceJ

I have several worksheets and I copy data from on temporary sheet into a
final sheet. I need to copy formulas that are in the new sheet down into
each of the new rows of data. The columas are not next to each other.

Here are the columns I need to copy the formula down in.

AF
AJ
AO
BP
Bq
BR
BS
BT

Here is a macro I recorded, but it would be fixed for only the current rows
and no more NEW data. How do I select the current column, minus header to
the end of data in that column? Column select won't work becuase of the
header....


Sub downfill()
'
' downfill Macro
' Macro recorded 9/22/2003 by
Range("AF2:AF845").Select
Range("AF845").Activate
Selection.FillDown
Columns("AF:AF").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Range("AJ2:AJ845").Select
Range("AJ845").Activate
Selection.FillDown
Columns("AJ:AJ").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("AO2:AO845").Select
Range("AO845").Activate
Selection.FillDown


End Sub


Thanks
Bruce
 
that is one of the mystyies I don't know, I was thinking filling from top to
bottom;. Or I can have the rows selected (as they are already selected when
I paste them in...

Bruce

Tom Ogilvy said:
You don't say how to tell where the new rows start and end.



Regards,
Tom Ogilvy

BruceJ said:
I have several worksheets and I copy data from on temporary sheet into a
final sheet. I need to copy formulas that are in the new sheet down into
each of the new rows of data. The columas are not next to each other.

Here are the columns I need to copy the formula down in.

AF
AJ
AO
BP
Bq
BR
BS
BT

Here is a macro I recorded, but it would be fixed for only the current rows
and no more NEW data. How do I select the current column, minus header to
the end of data in that column? Column select won't work becuase of the
header....


Sub downfill()
'
' downfill Macro
' Macro recorded 9/22/2003 by
Range("AF2:AF845").Select
Range("AF845").Activate
Selection.FillDown
Columns("AF:AF").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Range("AJ2:AJ845").Select
Range("AJ845").Activate
Selection.FillDown
Columns("AJ:AJ").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("AO2:AO845").Select
Range("AO845").Activate
Selection.FillDown


End Sub


Thanks
Bruce
Click to expand...
Click to expand...
 
I'm guessing that the number of rows depends on how much you paste into the
worksheet.

And for this example code, I guessed that you were pasting into column A.

Then I used the last cell that's filled in Column A to determine the last row to
be filled:

Option Explicit
Sub testme01()

Dim myCols As Variant
Dim iCtr As Long
Dim wks As Worksheet
Dim LastRow As Long
Dim FirstRow As Long

myCols = Array("AF", "AJ", "AO", "BP", "Bq", "BR", "BS", "BT")

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iCtr = LBound(myCols) To UBound(myCols)
.Range(.Cells(FirstRow, myCols(iCtr)), _
.Cells(LastRow, myCols(iCtr))).FillDown
Next iCtr
End With

End Sub
I have several worksheets and I copy data from on temporary sheet into a
final sheet. I need to copy formulas that are in the new sheet down into
each of the new rows of data. The columas are not next to each other.

Here are the columns I need to copy the formula down in.

AF
AJ
AO
BP
Bq
BR
BS
BT

Here is a macro I recorded, but it would be fixed for only the current rows
and no more NEW data. How do I select the current column, minus header to
the end of data in that column? Column select won't work becuase of the
header....

Sub downfill()
'
' downfill Macro
' Macro recorded 9/22/2003 by
Range("AF2:AF845").Select
Range("AF845").Activate
Selection.FillDown
Columns("AF:AF").Select
Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
Range("AJ2:AJ845").Select
Range("AJ845").Activate
Selection.FillDown
Columns("AJ:AJ").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("AO2:AO845").Select
Range("AO845").Activate
Selection.FillDown

End Sub

Thanks
Bruce
Click to expand...
 
Back
Top