bbennett77
08-23-2017, 01:46 PM
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
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