VBA Insert rows and fill formulas in worksheets

[ynot]

Hi guys

Apologies firstly as I am not excel savvy so bear with me.

What I want to do is write some code that inserts rows and copies dow
the last formula row in worksheets.

If my data in my first worksheet looks like this

A B
1 Porschers
2 Ferraris
3 Lamborghinis

I would like the macro to go to a worksheet titled "Porschers" and loo
for the last row in that worksheet and copy the last row of formul
down for 1 row only. Likewise it would then go to the "Ferraris
worksheet and copy down 2 rows. If it said 60 ferraris I would like i
to copy down 60 rows of formula. And so on....

I would love some help on this as it sounds easy to do but do not hav
any luck:confused

 

[Bill Schatzow]

I am looking for VBA code to set the print area from A1 to the last cell in
column M. I can do it easily for last cell used, but have not been able to
figure out how to select the last cell used in a particular column.

Thanks.

Bill

 

[Dave Peterson]

With ActiveSheet
.PageSetup.PrintArea = .Range("a1:m" _
& .Cells(.Rows.Count, "M").End(xlUp).Row).Address
End With

 

[Bill Schatzow]

Thank you.

With ActiveSheet
.PageSetup.PrintArea = .Range("a1:m" _
& .Cells(.Rows.Count, "M").End(xlUp).Row).Address
End With

 

[John Williams]

What I want to do is write some code that inserts rows and copies down
the last formula row in worksheets.

If my data in my first worksheet looks like this

A B
1 Porschers
2 Ferraris
3 Lamborghinis

I would like the macro to go to a worksheet titled "Porschers" and look
for the last row in that worksheet and copy the last row of formula
down for 1 row only. Likewise it would then go to the "Ferraris"
worksheet and copy down 2 rows. If it said 60 ferraris I would like it
to copy down 60 rows of formula. And so on....

Name your first worksheet Index, then add worksheets named Porsches,
Ferraris and Lamborghinis. In each of the 'car' sheets, create some
data with simple formula, e.g. in the A1 cells, type the number 1 and
in B1 cells type the formula =A1*10. Insert as many rows and columns
as required.

Now paste the following code into a module and run Macro1.

Sub Macro1()

Dim lastRow, iRow, numRows As Long
Dim worksheetName As String

'Determine the last row in the Index sheet

Worksheets("Index").Select
Range("A1").Select
lastRow = Cells(rows.Count, ActiveCell.Column).End(xlUp).row

'Update each worksheet with the specified number of rows

For iRow = 1 To lastRow
Worksheets("Index").Select
numRows = Cells(iRow, 1).Value
worksheetName = Cells(iRow, 2).Value
If worksheetExists(worksheetName, ThisWorkbook) And numRows >
0 Then
Call updateSheet(worksheetName, numRows)
End If
Next

Worksheets("Index").Select

End Sub

Sub updateSheet(worksheetName, numRows)
Dim lastRow, startCol, endCol As Long
Dim rangeStart, rangeEnd As Range

'Activate the specified sheet

Worksheets(worksheetName).Activate

'Determine the last row and column of data in the sheet

Range("A1").Select
lastRow = Cells(rows.Count, ActiveCell.Column).End(xlUp).row
endCol = Cells(ActiveCell.row, Columns.Count).End(xlToLeft).Column
startCol = 1

'Select all columns in the last row

Set rangeStart = Range(Cells(lastRow, startCol), Cells(lastRow,
endCol))

'Fill to specified number of rows

Set rangeEnd = Range(Cells(lastRow, startCol), Cells(lastRow +
numRows, endCol))
rangeStart.Select
Selection.AutoFill Destination:=rangeEnd, Type:=xlFillDefault

End Sub

Function worksheetExists(WSName As String, Optional WB As Workbook) As
Boolean
On Error Resume Next
worksheetExists = CBool(Len(IIf(WB Is Nothing, ActiveWorkbook,
WB).Worksheets(WSName).name))
End Function