Copy and Paste Macro help please

[bbennett77]

I am relatively new to Excel but learning quickly thanks to forums, and I am very new to macros. I Have a macro written (sourced as bits and pieces from other posts on forums) which copies data from a calculation sheet and pastes it to the next blank line in a summary sheet. The summary sheet is set up as one week per row, for 52 weeks. Everything works except in 4 columns I have a running total/average at the bottom for the year. Obviously in those 4 columns the data gets pasted below the totals, but I want them on the next blank line inside the annual summary table

This is the section from the macro that needs tweaking.. I need to copy B19-I19 from one sheet into the next blank line in columns G-N on the annual summary sheet, but there is totals formulas in G55-J55 at the bottom of the years data.

Sheets("Environmental calculation").Select
    Sheets("Environmental calculation").unprotect
    Range("B19:I19").Select
    Selection.Copy
    Sheets("Weekly observations").Select
    Sheets("Weekly observations").unprotect
    lMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

I also have the protect line at the end of the whole macro to finish off, but is there any way to reprotect it with a password through the macro? I will need this as there will be other people entering this data occasionally and would not want to lose any previous data on the sheet.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

TIA for any help with these issues

[offthelip]

try this:

Sub testmove()
With Worksheets("Environmental calculation")
   rowdata = Range(.Cells(19, 2), .Cells(19, 9))
End With
 With Worksheets("Weekly observations")
 Sheets("Weekly observations").Unprotect
   inarr = Range(.Cells(1, 1), .Cells(55, 10))
   For i = 54 To 1 Step -1
     If inarr(i, 7) <> "" Then
      blankrow = i + 1
      Exit For
     End If
   Next i
   
 Range(.Cells(blankrow, 7), .Cells(blankrow, 14)) = rowdata
 
 End With
Sheets("Weekly observations").Protect Password:="pass1"


End Sub

I forgot to put the unprotect /protect for the Environmental calculation tab

[bbennett77]

Perfect! Thank you very much