How can I indicate in a macro to which last sheet to return ?

[Ron092007]

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,C22,C33,E2:F2,E3:F3,G3:H3,G2:H2,I2: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

 

[Gary''s Student]

We need to remember where we were and then go back to there:

Sub routine()
Dim GoBack As Worksheet
Set GoBack = ActiveSheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' your code here
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
GoBack.Activate
End Sub

 

[Stefi]

One way:

....
lastsheet = ActiveSheet.Name
Sheets("Total Outputs").Select
....
Sheets(lastsheet).Select

Regards,
Stefi

„Ron092007†ezt írta:

 

[Ron092007]

Most grateful Gary. It works as a Swiss watch !

We need to remember where we were and then go back to there:

Sub routine()
Dim GoBack As Worksheet
Set GoBack = ActiveSheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' your code here
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
GoBack.Activate
End Sub

 

[Ron092007]

Thanks Stefi. ActiveSheet.Name...Should "Name" be the actual name of the
sheet ? or will it automatically use the name of the last sheet ?

Ron

 

[Stefi]

lastsheet = ActiveSheet.Name
stores the name of the active (last) sheet in variable lastsheet,

Sheets(lastsheet).Select
activates sheet name of which is stored in variable lastsheet.

Stefi

„Ron092007†ezt írta:

 

[Ron092007]

Thanks. Well understood.

We need to remember where we were and then go back to there:

Sub routine()
Dim GoBack As Worksheet
Set GoBack = ActiveSheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' your code here
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
GoBack.Activate
End Sub

 

[Ron092007]

Dear Gary, you seem to be very knowledgeable and wonder if you could help me
further, because I am stuck. Problem: the macro creates new rows for another
Expert costing line in the Input sheet. Then in the Total Output sheet
creates a corresponding new expert cost line. Since costing input will be
provided later (after macro is finished) I need to find a way to insert in
the new row in Total Output sheet the reference to the corresponding cost
cells in the Input sheets like =Outp.1!C13 but it could be Outp.1 to 10. With
my limited knowledge of macros I don't see a way to accomplish this. Would
you know ?
Many thanks in advance. Ron

 

[Per Jessen]

Hi Ron

The cell you want to link to in the Total Output sheet, is it the
active cell and active sheet when you start the previous posted
macro ?

Where shall the new row in the total output sheet be insereted? If you
have some totals in rows below the desired insert point, you could
name the cell and use it as reference.

Post your current code and comments, and I'll get back to you with a
solution.

Regards,
Per

 

[Ron092007]

Hi Per,

The Total Output sheet is a sheet without manual input. All details come
from 10 identical input sheets. When the user e.g decides an expert line is
needed in input sheet 1, he calls the macro to insert a new expert line in
the Input sheet (this part not yet finished). The same macro then goes to
Toal Output sheet and creates a new row for the new expert line from input
sheet 1. So far so good. With Gary's suggestion going back to the Input sheet
is no problem. But I am stuck how I can insert references in the cells of the
Total sheet to the relevant cells in the expert line in the Input sheet.
When the macro is called the active sheet is the Input sheet. There are
totals in other components of the Total sheet, but it may be a problem that
the number of expert rows to be inserted in the Total sheet cannot be
foreseen.
If you give me your email address I can send you the relevant Excel file,
which will make it clearer to understand. Mine is (e-mail address removed)
Many thanks.

Herewith the latest version of the macro:
ActiveSheet.Unprotect
Dim GoBack As Worksheet
Set GoBack = ActiveSheet

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("A1,B1,C22,C33,E2:F2,E3:F3,G3:H3,G2:H2,I2:J2,I3:J3,K2:L2,K3:L3").Select
ActiveCell.Offset(2, 12).Range("A1").Activate
Selection.ClearContents


Sheets("Total Outputs").Select
ActiveSheet.Unprotect
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

GoBack.Activate
End Sub