Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Show "Summary" when macro has run
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default Show "Summary" when macro has run
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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,276
Default Show "Summary" when macro has run
Hi,
Try

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


"winnie123" wrote:

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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default Show "Summary" when macro has run
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" wrote:

Hi,
Try

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


"winnie123" wrote:

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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,276
Default Show "Summary" when macro has run
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" wrote:

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" wrote:

Hi,
Try

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


"winnie123" wrote:

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.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default Show "Summary" when macro has run
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" wrote:

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" wrote:

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" wrote:

Hi,
Try

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


"winnie123" wrote:

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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
EXCEL allow 2 options on status bar e.g. show "Count" + "Sum" LEJM Excel Discussion (Misc queries) 2 November 15th 07 07:49 PM
Why won't the "record macro" button show on worksheet checkQ Excel Discussion (Misc queries) 2 April 5th 07 01:26 AM
if "a" selected from dropdown menu then show "K" in other cell LEGALMATTERS Excel Worksheet Functions 1 April 13th 06 06:05 PM
Pivot table "Group and Show Details" vs. "SubTotals" pgchop Excel Programming 0 February 1st 06 07:29 AM
conditional formula to show "open" or "closed" SBS Excel Worksheet Functions 6 January 28th 06 01:48 AM


All times are GMT +1. The time now is 08:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement