VBA clearing sheet below header

[pile-it Mark]

i have a code that functions great. after testing i realized i need it to clear contents below row 3.

row 1 is headings, row 2 is formulas, row 3 is a blank buffer, i would like to keep the format, but that could be ignored.

and i would need to tell my code to start posting on line 4

I am very new at VBA. 5 days learning so far. everything i have is copy and paste. with lots of help from this site, Yahoo, and trials. now i cant find the code that i read that discussed this. and doubt i could figure out where to place it if i found it.

Thanks

Option Explicit
Sub Copy1stRow()
' hiker95, 01/15/2014, ME750730 Default Re: copy of a single row from multiple worksheets into summary sheet within the same workbook
 ' modified by pile-it Mark with help from Ashutosh Kumar 01/06/2017
Dim ws As Worksheet, nr As Long
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "2017 TOTALS" _
And Not ws.Name = "distance table" _
And Not ws.Name = "Flight Log 2017 Original" _
And Not ws.Name = "Summary" _
And Not ws.Name = "Master" _
And Not ws.Name = "vba test" Then
nr = Sheets("2017 TOTALS").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Rows(1).Copy
Sheets("2017 TOTALS").Rows(nr).PasteSpecial Paste:=xlPasteValues
End If
Next ws
End Sub

 

[dmt32]

Hi,
Glad it helped you.

i have been doing all my work i individual modules. is there a better way? or it really creators choice?

Coding is in general, very much a matter of personal style – you will see many contributors on this board offer a solution to a problem & majority will be presented in very different ways. That does mean any are wrong but just shows different ways you can achieve same result.

For large applications, placing your procedures / functions in separate modules can be a good idea as makes code easier to read & follow – you should also give each module a meaningful name. I tend to use the procedure name followed by _Code or _Function.
Also, if using separate modules, you will need to ensure that any variables from the calling procedure that are required in the called code are made available – way many do this is to make your variables public but personally, I try an avoid using public variables. Better if can to pass a copy (ByVal) of the variable via an argument (as I did with your sort code)

You can insert other procedures to call if required – if they are private you will need to specify the module name as well but again personally, it’s not a method I use.

Hope helpful

Dave.