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