I am writing an Excel 2002 template and need to reformat text data which is
initially copied into an empty sheet (Imported Data). The number of columns
is constant, but the number of rows will vary.
My second sheet (Converted Data) contains lookups or formulae to manipulate
the text strings. These are all in row 2.

The formulae in 'Converted Data' are copied down manually to match the
number of rows in 'Imported Data', thus converting all of the data.

How can I achieve this automatically using VBA?


If I right click on the 'Converted Data' tab and 'View Code', I assume I can
write the VBA code here ....


Private Sub Worksheet_Activate()
' VBA code
End Sub

Hi,

I'm not sure putting this in Worksheet_Activate is the right thing to do
because it will execute every time you select the worksheet.

You don't give details of columns or formula so here's a way of entering a
formula and filling down as far as there are data in an adjacent column.

Private Sub Worksheet_Activate()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("B1").Formula = "=A1"
Range("B1:B" & LastRow).FillDown
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Grey Old Man" wrote:

I am writing an Excel 2002 template and need to reformat text data which is
initially copied into an empty sheet (Imported Data). The number of columns
is constant, but the number of rows will vary.
My second sheet (Converted Data) contains lookups or formulae to manipulate
the text strings. These are all in row 2.

The formulae in 'Converted Data' are copied down manually to match the
number of rows in 'Imported Data', thus converting all of the data.

How can I achieve this automatically using VBA?


If I right click on the 'Converted Data' tab and 'View Code', I assume I can
write the VBA code here ....


Private Sub Worksheet_Activate()
' VBA code
End Sub

I need to count the rows in a sheet named 'Imported Data' where the column
range is A:R.

The conversion is applied in a sheet named 'Converted Data' where the
formulae are all in row 2.

I need to automatically fill down from 'Converted Data'!A2:R2 until it
reaches the row count of 'Imported Data'

Sub Auto_Fill()
Dim lRow As Long
With Sheets("Converted Data")
lRow = Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:R" & lRow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP


On Mon, 15 Mar 2010 06:30:01 -0700, Grey Old Man
wrote:

I need to count the rows in a sheet named 'Imported Data' where the column
range is A:R.

The conversion is applied in a sheet named 'Converted Data' where the
formulae are all in row 2.

I need to automatically fill down from 'Converted Data'!A2:R2 until it
reaches the row count of 'Imported Data'

I eventually solved this with ...

Private Sub Worksheet_Activate()
Dim LastRow As Long
LastRow = Sheet1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheet2.Range("A2:R" & LastRow).FillDown
End Sub