Macro Copy/Past To Bottom Row of Growing Skip Column Header

[David Foran]

I have a macro that copies a row of formulas from row 1. Then skips over a column header in row 2 then pastes the formulas into rows 3 through to the bottom of a data dump that grows monthly. I then recalculate before copying again and pasting as values. I am using the command "Range(Selection, Selection.End(xlDown)).Select" but it usually leaves a few blank rows before the bottom. I can't understand why this command doesn't work as I think it should. Any help would be appreciated.


PS. "Report_Section_Lookup_Formulas" is a named range of formulas to be copied and "Top_Left_Report_Section" is the named cell at the top left of the range I want copied to.


Sub Rpt_Section_CopyPaste()
'
' Rpt_Section_CopyPaste Macro
'


Application.ScreenUpdating = False


Range("Report_Section_Lookup_Formulas").Select
Selection.Copy
Range("Top_Left_Report_Section").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[cerfani]

The function end simply travels to the last cell with data in the direction chosen so you need to ensure you have no blanks before you hit where you want end to get to. And also cells that appear blank may be formatted weird or use characters like spaces.