macro
 

My workbook contains one sheet "Total" and multiple identical sheets, each
for the same variety of inputs, to be totalled in the Total sheet. For one
item I need to insert multiple rows in several input sheets, which I do with
a macro. When I have inserted the rows needed in the input sheet and
thereafter in the Total sheet, how can I indicate in the macro to which last
input sheet to return ? Any suggestions would be much appreciated.

ActiveSheet.Unprotect
ActiveCell.Rows("1:3").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-3, 0).Rows("1:3").EntireRow.Select
Selection.Copy
ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Range("o2:T2").Select
Selection.Copy

Range("A1,B1,C2:D2,C3:D3,E2:F2,E3:F3,G3:H3,G2:H2,I 2:J2,I3:J3,K2:L2,K3:L3").Select
ActiveCell.Offset(2, 12).Range("A1").Activate
Selection.ClearContents


Sheets("Total Outputs").Select
ActiveSheet.Unprotect Password:="TCOM"
myRange = Application.InputBox(prompt:="Select", Type:=8).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Rows("1:1").Range("c1,g1:l1").Select
Selection.ClearContents

macro
 

Declare a variable as a worksheet type.

In your macro, set this varable's value to the latest sheet, (I take it that
is the active one when you start the macro), and at the end, set this sheet
as active.

Dim wsLatest as worksheet

wsLatest.Activate

"Ron092007" wrote:

My workbook contains one sheet "Total" and multiple identical sheets, each
for the same variety of inputs, to be totalled in the Total sheet. For one
item I need to insert multiple rows in several input sheets, which I do with
a macro. When I have inserted the rows needed in the input sheet and
thereafter in the Total sheet, how can I indicate in the macro to which last
input sheet to return ? Any suggestions would be much appreciated.

ActiveSheet.Unprotect
ActiveCell.Rows("1:3").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-3, 0).Rows("1:3").EntireRow.Select
Selection.Copy
ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Range("o2:T2").Select
Selection.Copy

Range("A1,B1,C2:D2,C3:D3,E2:F2,E3:F3,G3:H3,G2:H2,I 2:J2,I3:J3,K2:L2,K3:L3").Select
ActiveCell.Offset(2, 12).Range("A1").Activate
Selection.ClearContents


Sheets("Total Outputs").Select
ActiveSheet.Unprotect Password:="TCOM"
myRange = Application.InputBox(prompt:="Select", Type:=8).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Rows("1:1").Range("c1,g1:l1").Select
Selection.ClearContents

macro
 

Does this help? I made the code easier to understand.

ActiveSheet.Unprotect
Set Sht = Activesheet
StartRow = activecell.Row
StartCol = activecell.Column
with Sht
.Rows(StartRow & ":" & (StartRow + 3)).EntireRow.Insert _
Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
.Rows((Startrow - 3) & ":" & (StartRow - 1)).Copy
Destination:=.Rows(StartRow)
Application.CutCopyMode = False
'The line below is doing nothing.
'I commented it out
.cells(StartRow + 1,StartCol).Offset(0, 2).Range("o2:T2").copy


'The line below is doing nothing.
'I commented it out

'sht.Range("A1,B1,C2:D2,C3:D3,E2:F2,E3:F3,G3:H3,G2 :H2,I2:J2,I3:J3,K2:L2,K3:L3").Select
'Why not just go to cell M3. the active cell was at A1 from above line
'ActiveCell.Offset(2, 12).Range("A1").Activate
Range("M3").ClearContents
end sht

with Sheets("Total Outputs")
.Unprotect Password:="TCOM"
set myRange = Application.InputBox(prompt:="Select", Type:=8)
MyRow = MyRange.Row
.Rows(MyRow).Insert _
Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
.Rows(MyRow - 1).Copy _
Destination:=.Rows(MyRow)
Application.CutCopyMode = False
.Rows(MyRow).Range("c1,g1:l1").ClearContents

end with

"Ron092007" wrote:

My workbook contains one sheet "Total" and multiple identical sheets, each
for the same variety of inputs, to be totalled in the Total sheet. For one
item I need to insert multiple rows in several input sheets, which I do with
a macro. When I have inserted the rows needed in the input sheet and
thereafter in the Total sheet, how can I indicate in the macro to which last
input sheet to return ? Any suggestions would be much appreciated.

ActiveSheet.Unprotect
ActiveCell.Rows("1:3").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-3, 0).Rows("1:3").EntireRow.Select
Selection.Copy
ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Range("o2:T2").Select
Selection.Copy

Range("A1,B1,C2:D2,C3:D3,E2:F2,E3:F3,G3:H3,G2:H2,I 2:J2,I3:J3,K2:L2,K3:L3").Select
ActiveCell.Offset(2, 12).Range("A1").Activate
Selection.ClearContents


Sheets("Total Outputs").Select
ActiveSheet.Unprotect Password:="TCOM"
myRange = Application.InputBox(prompt:="Select", Type:=8).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Rows("1:1").Range("c1,g1:l1").Select
Selection.ClearContents