Macro to copy formula till second last row

[razgra1z]

Hello All,

Please guide me how I can adjust these lines to get formula copy till second last cell and not the last one.

Selection.AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
Range(Selection, Selection.End(xlDown)).Select

Thanks in advance.

 

[Fluff]

How about

Selection.AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Offset(-1).Row), Type:=xlFillDefault

 

[razgra1z]

How about

Selection.AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Offset(-1).Row), Type:=xlFillDefault

Thanks a lot Fluff for the correction

 

[Fluff]

You're welcome & thanks for the feedback

 

[razgra1z]

Hi Fluff,

I want sum in last row and make it bold. I've found following VBA code, how I can insert it in my current macro and make sum bold.

Dim lLastRow As Long
With Sheets("Sheet1") 'put your sheet name here
lLastRow = .Range("X" & .Rows.Count).End(xlUp).Row
.Range("X" & lLastRow + 1).Formula = "=Sum(X2:X" & lLastRow & ")"
End With

Thanks in advance.

 

[Fluff]

Add this

     .Range("X" & lLastRow + 1).Formula = "=Sum(X2:X" & lLastRow & ")"
     .Range("X" & lLastRow + 1).Font.Bold=True
End With

 

[razgra1z]

Range("X2").Select
Selection.AutoFill Destination:=Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Offset(-1).Row), Type:=xlFillDefault
Range(Selection, Selection.End(xlDown)).Select
Columns("X:X").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Range("X" & lLastRow + 1).Formula = "=Sum(X2:X" & lLastRow & ")"
.Range("X" & lLastRow + 1).Font.Bold = True
Range("X1").Select
ChDir "C:\Users\Adam\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Adam\Desktop\Inventory Sales Today.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Summary").Select

Here is the macro...I've inserted what you've suggested. After copying formula to second last row, I need to convert those formula into values and then I want sum in last row with bold text. The lines I've inserted are not working...may be i am not putting them in right place. Please advise.

 

[Fluff]

You have removed the With Statement, that's why it's not working

 

[razgra1z]

oohhhh.....thanks its working now

 

[Fluff]

You're welcome & thanks for the feedback