| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I indicate in a macro to which last sheet to return ?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
We need to remember where we were and then go back to the
Sub routine() Dim GoBack As Worksheet Set GoBack = ActiveSheet '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' ' ' your code here ' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' GoBack.Activate End Sub -- Gary''s Student - gsnu200841 "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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
One way:
.... lastsheet = ActiveSheet.Name Sheets("Total Outputs").Select .... Sheets(lastsheet).Select Regards, Stefi Ron092007 ezt *rta: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
Most grateful Gary. It works as a Swiss watch !
"Gary''s Student" wrote: We need to remember where we were and then go back to the Sub routine() Dim GoBack As Worksheet Set GoBack = ActiveSheet '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' ' ' your code here ' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' GoBack.Activate End Sub -- Gary''s Student - gsnu200841 "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
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" wrote: One way: ... lastsheet = ActiveSheet.Name Sheets("Total Outputs").Select ... Sheets(lastsheet).Select Regards, Stefi Ron092007 ezt *rta: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
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: 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" wrote: One way: ... lastsheet = ActiveSheet.Name Sheets("Total Outputs").Select ... Sheets(lastsheet).Select Regards, Stefi Ron092007 ezt *rta: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
Thanks. Well understood.
"Gary''s Student" wrote: We need to remember where we were and then go back to the Sub routine() Dim GoBack As Worksheet Set GoBack = ActiveSheet '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' ' ' your code here ' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' GoBack.Activate End Sub -- Gary''s Student - gsnu200841 "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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
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 "Gary''s Student" wrote: We need to remember where we were and then go back to the Sub routine() Dim GoBack As Worksheet Set GoBack = ActiveSheet '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' ' ' your code here ' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' GoBack.Activate End Sub -- Gary''s Student - gsnu200841 "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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I indicate in a macro to which last sheet to return ?
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 On 29 Mar., 06:19, Ron092007 wrote: 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 "Gary''s Student" wrote: We need to remember where we were and then go back to the Sub routine() Dim GoBack As Worksheet Set GoBack = ActiveSheet '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' ' ' * * *your code here ' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''' GoBack.Activate End Sub -- Gary''s Student - gsnu200841 "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- Skjul tekst i anfrselstegn - - Vis tekst i anfrselstegn - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to return user to previously viewed sheet | Excel Programming | |||
| No RETURN() or HALT() function found on macro sheet | Excel Discussion (Misc queries) | |||
| Beginner help! error no return() or halt() function found on macro sheet | Excel Programming | |||
| Macro to return to Current Sheet and Cell | Excel Programming | |||
| Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
Linear Mode
