Show "Summary" when macro has run

W

winnie123

Hi

I have a macro which does various copy and paste on specific sheets within
the workbook.

How can I get the macro to end so that the "summary" sheet in cell A1 is on
view.

Hope that make sense

I tried to add the line below and put that at the end but it does not seem
to work

Sheets("Summary").Select
Range("A1:A1").Select

End Sub

Please help.
 
W

winnie123

Hi,

I tried this and unfortunately it didnt work.

My full code is below

Its a bit messy as I am just trying to get into macro's. Also if I just
postion to the line within the code it goes to the summary sheets and select
cell A1.

Worksheets("Summary").Select
Range("A1").Select


Sub Update_All()



Sheets("openorders").Unprotect
LastRow = Sheets("openorders").Range("A" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("M2").Copy _
Destination:=Sheets("openorders").Range("M3:M" & LastRow)

LastRow = Sheets("openorders").Range("V" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("AD2").Copy _
Destination:=Sheets("openorders").Range("AD3:AD" & LastRow)
Sheets("openorders").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("I2").Copy _
Destination:=Sheets("shipped").Range("I3:I" & LastRow)

LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("K2").Copy _
Destination:=Sheets("shipped").Range("K3:K" & LastRow)
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("sheet1").Unprotect
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("E2:G2").Copy _
Destination:=Sheets("Sheet1").Range("E3:G" & LastRow)


Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Select
Range("I1:I" & LastRow).Select
Selection.Copy
Sheets("Sheet1").Select
Range("D1:D" & LastRow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

Sheets("sheet1").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub


Sub Delete_with_Autofilter()
Sheets("Sheet1").Select
Dim DeleteValue As String
Dim rng As Range

DeleteValue = ">60"

With Sheets("Sheet1")
Sheets("Sheet1").Unprotect
..Range("G1:G1000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
..SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
..AutoFilterMode = False
Sheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

Sub copy_()
Sheets("Summary").Select



Sheets("Summary").Unprotect
Range("K4:N4").Select
Selection.Copy
Range("K5:N15").Select
ActiveSheet.Paste
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Range("K20:N30").Select
ActiveSheet.Paste
Range("K34:N34").Select
Application.CutCopyMode = False
Selection.Copy
Range("K35:N45").Select
ActiveSheet.Paste
Range("K49:N49").Select
Application.CutCopyMode = False
Selection.Copy
Range("K50:N60").Select
ActiveSheet.Paste
Range("K64:N64").Select
Application.CutCopyMode = False
Selection.Copy
Range("K65:N75").Select
ActiveSheet.Paste
Range("K79:N79").Select
Application.CutCopyMode = False
Selection.Copy
Range("K80:N90").Select
ActiveSheet.Paste
Range("K94:N94").Select
Application.CutCopyMode = False
Selection.Copy
Range("K95:N105").Select
ActiveSheet.Paste
Range("K109:N109").Select
Application.CutCopyMode = False
Selection.Copy
Range("K110:N120").Select
ActiveSheet.Paste
Range("K124:N124").Select
Application.CutCopyMode = False
Selection.Copy
Range("K125:N135").Select
ActiveSheet.Paste
Range("K139:N139").Select
Application.CutCopyMode = False
Selection.Copy
Range("K140:N150").Select
ActiveSheet.Paste
Range("K154:N154").Select
Application.CutCopyMode = False
Selection.Copy
Range("K155:N165").Select
ActiveSheet.Paste
Range("K169:N169").Select
Application.CutCopyMode = False
Selection.Copy
Range("K170:N180").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With

Worksheets("Summary").Select
Range("A1").Select

End Sub
 
E

Eduardo

Hi,
you sent 2 codes in the 2nd one already you are in the Summary sheet so you
have not to select it just do Range("A").Select

In the first one you should be entering the given code, just one question do
you have the summary hidden?

winnie123 said:
Hi,

I tried this and unfortunately it didnt work.

My full code is below

Its a bit messy as I am just trying to get into macro's. Also if I just
postion to the line within the code it goes to the summary sheets and select
cell A1.

Worksheets("Summary").Select
Range("A1").Select


Sub Update_All()



Sheets("openorders").Unprotect
LastRow = Sheets("openorders").Range("A" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("M2").Copy _
Destination:=Sheets("openorders").Range("M3:M" & LastRow)

LastRow = Sheets("openorders").Range("V" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("AD2").Copy _
Destination:=Sheets("openorders").Range("AD3:AD" & LastRow)
Sheets("openorders").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("I2").Copy _
Destination:=Sheets("shipped").Range("I3:I" & LastRow)

LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("K2").Copy _
Destination:=Sheets("shipped").Range("K3:K" & LastRow)
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("sheet1").Unprotect
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("E2:G2").Copy _
Destination:=Sheets("Sheet1").Range("E3:G" & LastRow)


Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Select
Range("I1:I" & LastRow).Select
Selection.Copy
Sheets("Sheet1").Select
Range("D1:D" & LastRow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

Sheets("sheet1").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub


Sub Delete_with_Autofilter()
Sheets("Sheet1").Select
Dim DeleteValue As String
Dim rng As Range

DeleteValue = ">60"

With Sheets("Sheet1")
Sheets("Sheet1").Unprotect
.Range("G1:G1000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
Sheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

Sub copy_()
Sheets("Summary").Select



Sheets("Summary").Unprotect
Range("K4:N4").Select
Selection.Copy
Range("K5:N15").Select
ActiveSheet.Paste
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Range("K20:N30").Select
ActiveSheet.Paste
Range("K34:N34").Select
Application.CutCopyMode = False
Selection.Copy
Range("K35:N45").Select
ActiveSheet.Paste
Range("K49:N49").Select
Application.CutCopyMode = False
Selection.Copy
Range("K50:N60").Select
ActiveSheet.Paste
Range("K64:N64").Select
Application.CutCopyMode = False
Selection.Copy
Range("K65:N75").Select
ActiveSheet.Paste
Range("K79:N79").Select
Application.CutCopyMode = False
Selection.Copy
Range("K80:N90").Select
ActiveSheet.Paste
Range("K94:N94").Select
Application.CutCopyMode = False
Selection.Copy
Range("K95:N105").Select
ActiveSheet.Paste
Range("K109:N109").Select
Application.CutCopyMode = False
Selection.Copy
Range("K110:N120").Select
ActiveSheet.Paste
Range("K124:N124").Select
Application.CutCopyMode = False
Selection.Copy
Range("K125:N135").Select
ActiveSheet.Paste
Range("K139:N139").Select
Application.CutCopyMode = False
Selection.Copy
Range("K140:N150").Select
ActiveSheet.Paste
Range("K154:N154").Select
Application.CutCopyMode = False
Selection.Copy
Range("K155:N165").Select
ActiveSheet.Paste
Range("K169:N169").Select
Application.CutCopyMode = False
Selection.Copy
Range("K170:N180").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With

Worksheets("Summary").Select
Range("A1").Select

End Sub



Eduardo said:
Hi,
Try

Worksheets("Summary").Select
Range("A1").Select
Click to expand...
Click to expand...
 
W

winnie123

I created 3 separate codes and then I pasted them into one macro.

I have now taken out the
Sub Delete_with_Autofilter()
Sub copy_()
And all the end Subs within leaving the last one.

And it nows seems to do the trick

Thanks for your help.

Eduardo said:
Hi,
you sent 2 codes in the 2nd one already you are in the Summary sheet so you
have not to select it just do Range("A").Select

In the first one you should be entering the given code, just one question do
you have the summary hidden?

winnie123 said:
Hi,

I tried this and unfortunately it didnt work.

My full code is below

Its a bit messy as I am just trying to get into macro's. Also if I just
postion to the line within the code it goes to the summary sheets and select
cell A1.

Worksheets("Summary").Select
Range("A1").Select


Sub Update_All()



Sheets("openorders").Unprotect
LastRow = Sheets("openorders").Range("A" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("M2").Copy _
Destination:=Sheets("openorders").Range("M3:M" & LastRow)

LastRow = Sheets("openorders").Range("V" & Rows.Count).End(xlUp).Row
Sheets("openorders").Range("AD2").Copy _
Destination:=Sheets("openorders").Range("AD3:AD" & LastRow)
Sheets("openorders").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("I2").Copy _
Destination:=Sheets("shipped").Range("I3:I" & LastRow)

LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Range("K2").Copy _
Destination:=Sheets("shipped").Range("K3:K" & LastRow)
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Sheets("sheet1").Unprotect
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("E2:G2").Copy _
Destination:=Sheets("Sheet1").Range("E3:G" & LastRow)


Sheets("shipped").Unprotect
LastRow = Sheets("shipped").Range("A" & Rows.Count).End(xlUp).Row
Sheets("shipped").Select
Range("I1:I" & LastRow).Select
Selection.Copy
Sheets("Sheet1").Select
Range("D1:D" & LastRow).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

Sheets("sheet1").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("shipped").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub


Sub Delete_with_Autofilter()
Sheets("Sheet1").Select
Dim DeleteValue As String
Dim rng As Range

DeleteValue = ">60"

With Sheets("Sheet1")
Sheets("Sheet1").Unprotect
.Range("G1:G1000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
Sheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

Sub copy_()
Sheets("Summary").Select



Sheets("Summary").Unprotect
Range("K4:N4").Select
Selection.Copy
Range("K5:N15").Select
ActiveSheet.Paste
Range("K19:N19").Select
Application.CutCopyMode = False
Selection.Copy
Range("K20:N30").Select
ActiveSheet.Paste
Range("K34:N34").Select
Application.CutCopyMode = False
Selection.Copy
Range("K35:N45").Select
ActiveSheet.Paste
Range("K49:N49").Select
Application.CutCopyMode = False
Selection.Copy
Range("K50:N60").Select
ActiveSheet.Paste
Range("K64:N64").Select
Application.CutCopyMode = False
Selection.Copy
Range("K65:N75").Select
ActiveSheet.Paste
Range("K79:N79").Select
Application.CutCopyMode = False
Selection.Copy
Range("K80:N90").Select
ActiveSheet.Paste
Range("K94:N94").Select
Application.CutCopyMode = False
Selection.Copy
Range("K95:N105").Select
ActiveSheet.Paste
Range("K109:N109").Select
Application.CutCopyMode = False
Selection.Copy
Range("K110:N120").Select
ActiveSheet.Paste
Range("K124:N124").Select
Application.CutCopyMode = False
Selection.Copy
Range("K125:N135").Select
ActiveSheet.Paste
Range("K139:N139").Select
Application.CutCopyMode = False
Selection.Copy
Range("K140:N150").Select
ActiveSheet.Paste
Range("K154:N154").Select
Application.CutCopyMode = False
Selection.Copy
Range("K155:N165").Select
ActiveSheet.Paste
Range("K169:N169").Select
Application.CutCopyMode = False
Selection.Copy
Range("K170:N180").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Summary").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With

Worksheets("Summary").Select
Range("A1").Select

End Sub



Eduardo said:
Hi,
Try

Worksheets("Summary").Select
Range("A1").Select


:

Hi

I have a macro which does various copy and paste on specific sheets within
the workbook.

How can I get the macro to end so that the "summary" sheet in cell A1 is on
view.

Hope that make sense

I tried to add the line below and put that at the end but it does not seem
to work

Sheets("Summary").Select
Range("A1:A1").Select

End Sub

Please help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to count words in a section, excluding footnotes and end notes 1
VBA for Dependents shortcut menu 0
OnTime Change_Event macro AND copy to sheet2 macro trouble 7
Macro will copy formula instead of value 6
cannot insert row in vba 1
Copying same data range to a summary sheet 8
Copy worksheet, trying to paste to new workbook error assistance. 1
Formatting and copy or copy and formatting ?? 2

Top