Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Meaning of some VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 65
Default Meaning of some VBA
Hello,

About a year ago, with the help of this forum I was able to make a macro to
transfer from an extract sheets ( A00) to and interface sheet ( A00 (2)).

Now there is an additional sheet created by the accounting software named E30.

I tried to refresh again my mine, but I almost forgot, can anyone help me?
the meaning of this VBA:
1.ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Selection.Copy
2. ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
3.ActiveWindow.ScrollWorkbookTabs Sheets:=1
4.Application.CutCopyMode = False

Or is there any way we can slow down the process when we run the macro, so
that we can see what it does?

This is the VBA which is working but, I do not know how to insert inthe VBA,
the Sheet E30 extract and E30(20) interface sheets:

Sub AutoShape10_Click()
'
' AutoShape10_Click Macro
' Macro recorded 5/11/2007 by Frank
' This is to copy data from extracts to Interface sheet

'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("A00").Select
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("A00 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("E00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E00 (2)").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("S20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S20 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("S10 (2)").Select
Cells.Select
Sheets("S10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S10 (2)").Select
ActiveSheet.Paste
Sheets("M10 (2)").Select
Cells.Select
Sheets("M10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M10 (2)").Select
ActiveSheet.Paste
Sheets("M00 (2)").Select
Cells.Select
Sheets("M00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M00 (2)").Select
ActiveSheet.Paste
Sheets("E20 (2)").Select
Cells.Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("E20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E20 (2)").Select
ActiveSheet.Paste
Sheets("E10 (2)").Select
Cells.Select
Sheets("E10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E10 (2)").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
End Sub

--
H. Frank Situmorang
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Meaning of some VBA
It looks well over-cooked to me, all you need is

Sub AutoShape10_Click()
Sheets("A00").Cells.Copy Sheets("A00 (2)").Cells
Sheets("E00").Cells.Copy Sheets("E00 (2)").Cells
Sheets("S20").Cells.Copy Sheets("S20 (2)").Cells
Sheets("S10").Cells.Copy Sheets("S10 (2)").Cells
Sheets("M10").Cells.Copy Sheets("M10 (2)").Cells
Sheets("M00").Cells.Copy Sheets("M00 (2)").Cells
Sheets("E20").Cells.Copy Sheets("E20 (2)").Cells
Sheets("E10").Cells.Copy Sheets("E10 (2)").Cells
Sheets("SUMMARYWBLA").Select
End Sub

which you might find simpler to adapt.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Hello,

About a year ago, with the help of this forum I was able to make a macro
to
transfer from an extract sheets ( A00) to and interface sheet ( A00 (2)).

Now there is an additional sheet created by the accounting software named
E30.

I tried to refresh again my mine, but I almost forgot, can anyone help me?
the meaning of this VBA:
1.ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Selection.Copy
2. ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
3.ActiveWindow.ScrollWorkbookTabs Sheets:=1
4.Application.CutCopyMode = False

Or is there any way we can slow down the process when we run the macro, so
that we can see what it does?

This is the VBA which is working but, I do not know how to insert inthe
VBA,
the Sheet E30 extract and E30(20) interface sheets:

Sub AutoShape10_Click()
'
' AutoShape10_Click Macro
' Macro recorded 5/11/2007 by Frank
' This is to copy data from extracts to Interface sheet

'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("A00").Select
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("A00 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("E00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E00 (2)").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("S20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S20 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("S10 (2)").Select
Cells.Select
Sheets("S10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S10 (2)").Select
ActiveSheet.Paste
Sheets("M10 (2)").Select
Cells.Select
Sheets("M10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M10 (2)").Select
ActiveSheet.Paste
Sheets("M00 (2)").Select
Cells.Select
Sheets("M00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M00 (2)").Select
ActiveSheet.Paste
Sheets("E20 (2)").Select
Cells.Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("E20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E20 (2)").Select
ActiveSheet.Paste
Sheets("E10 (2)").Select
Cells.Select
Sheets("E10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E10 (2)").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
End Sub

--
H. Frank Situmorang


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 65
Default Meaning of some VBA
Thanks Bob for your great help. In fact my specialty is an accountant, so I
am so thankful for your help.

The way I made macro is by recording macro., the following maybe could be an
overcooked too?:
Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so that
we can sort

'
Sheets("A00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E00
Sheets("E00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E10
Sheets("E10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E20
Sheets("E20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E30
Sheets("E30 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute M00
Sheets("M00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute M10
Sheets("M10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute S10
Sheets("S10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute S20
Sheets("S20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub
--
H. Frank Situmorang


"Bob Phillips" wrote:

It looks well over-cooked to me, all you need is

Sub AutoShape10_Click()
Sheets("A00").Cells.Copy Sheets("A00 (2)").Cells
Sheets("E00").Cells.Copy Sheets("E00 (2)").Cells
Sheets("S20").Cells.Copy Sheets("S20 (2)").Cells
Sheets("S10").Cells.Copy Sheets("S10 (2)").Cells
Sheets("M10").Cells.Copy Sheets("M10 (2)").Cells
Sheets("M00").Cells.Copy Sheets("M00 (2)").Cells
Sheets("E20").Cells.Copy Sheets("E20 (2)").Cells
Sheets("E10").Cells.Copy Sheets("E10 (2)").Cells
Sheets("SUMMARYWBLA").Select
End Sub

which you might find simpler to adapt.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Hello,

About a year ago, with the help of this forum I was able to make a macro
to
transfer from an extract sheets ( A00) to and interface sheet ( A00 (2)).

Now there is an additional sheet created by the accounting software named
E30.

I tried to refresh again my mine, but I almost forgot, can anyone help me?
the meaning of this VBA:
1.ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Selection.Copy
2. ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
3.ActiveWindow.ScrollWorkbookTabs Sheets:=1
4.Application.CutCopyMode = False

Or is there any way we can slow down the process when we run the macro, so
that we can see what it does?

This is the VBA which is working but, I do not know how to insert inthe
VBA,
the Sheet E30 extract and E30(20) interface sheets:

Sub AutoShape10_Click()
'
' AutoShape10_Click Macro
' Macro recorded 5/11/2007 by Frank
' This is to copy data from extracts to Interface sheet

'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("A00").Select
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("A00 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("E00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E00 (2)").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("S20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S20 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("S10 (2)").Select
Cells.Select
Sheets("S10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S10 (2)").Select
ActiveSheet.Paste
Sheets("M10 (2)").Select
Cells.Select
Sheets("M10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M10 (2)").Select
ActiveSheet.Paste
Sheets("M00 (2)").Select
Cells.Select
Sheets("M00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M00 (2)").Select
ActiveSheet.Paste
Sheets("E20 (2)").Select
Cells.Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("E20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E20 (2)").Select
ActiveSheet.Paste
Sheets("E10 (2)").Select
Cells.Select
Sheets("E10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E10 (2)").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
End Sub

--
H. Frank Situmorang



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Meaning of some VBA
Frank,

When you record a macro, the recorder copie all actions, some of which may
be what you did but are not necessary for the job, and also takes a
necessarily singular, simplistic approach to it.

As such, statements like

ActiveWindow.SmallScroll Down:=72

are totally redundant in macros, and can be removed.

It tends to repeat things too, so you may see code like this repeated over
and over, which if needed at all is only neede once at the end

Application.CutCopyMode = False

When you set a value in the recorder, it records it in this manner

Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"

which can be reduced to

Range("N9").FormulaR1C1 = "=+R[1]C[-12]"

or even

Range("N9").Value= "=+R[1]C[-12]"

in other words, no need to select it.

And again, you rarely need to select, so instead of

Range("P9").Select
Selection.Copy

you can use

Range("P9")..Copy

and instead of

Range("Q9:X9").Select
ActiveSheet.Paste

you can use

Range("Q9:X9").Paste

or even better, combine the 4 lines into one, that is

Range("P9").Copy Range("Q9:X9")

which is simpler to read, and more efficient without the selecting.

Taking your code as an example this set, which is repeatable on different
ranges throughout your code

'Execute S20
Sheets("S20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False

can be reduced to

'Execute S20
Sheets("S20 (2)").Select
Range("N9").Value = "=+R[1]C[-12]"
Range("O9").Value = "=+R[2]C[-13]"
Range("P9").Value = "=+R[1]C[-13]"
Range("P9").Copy Range("Q9:X9")
Range("N9:X11").Copy Range("N12:N1004")
Range("N9:X1002").Value = Range("N9:X1002").Value

or even remove the sheet select by using with


'Execute S20
With Sheets("S20 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With

So overall we now have

Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort

'
With Sheets("A00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E00
With Sheets("E00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E10
With Sheets("E10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E20
With Sheets("E20 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E30
With Sheets("E30 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute M00
With Sheets("M00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute M10
With Sheets("M10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute S10
With Sheets("S10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute S20
With Sheets("S20 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With

Sheets("SUMMARYWBLA").Select

Range("H2").Select
End Sub

But here we see the code is doing the same thing to different sheets, so it
would be better to put this into a separate variable function, like so

Private Function UpdateSheet(ByRef sh As Worksheet)

With sh

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function

Which is called like so

Call UdateSheet(Sheets("S20 (2)"))

The whole code then reduces to

Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort

'
Call UpdateSheet(Sheets("A00 (2)"))
Call UpdateSheet(Sheets("E00 (2)"))
Call UpdateSheet(Sheets("E10 (2)"))
Call UpdateSheet(Sheets("E20 (2)"))
Call UpdateSheet(Sheets("E30 (2)"))
Call UpdateSheet(Sheets("M00 (2)"))
Call UpdateSheet(Sheets("M10 (2)"))
Call UpdateSheet(Sheets("S10 (2)"))
Call UpdateSheet(Sheets("S20 (2)"))

Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub

Private Function UpdateSheet(ByRef sh As Worksheet)

With sh

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function

which is simpler, more readable, more maintaiabe, and faster.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Frank Situmorang" wrote in message
...
Thanks Bob for your great help. In fact my specialty is an accountant, so
I
am so thankful for your help.

The way I made macro is by recording macro., the following maybe could be
an
overcooked too?:
Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
that
we can sort

'
Sheets("A00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E00
Sheets("E00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E10
Sheets("E10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E20
Sheets("E20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute E30
Sheets("E30 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute M00
Sheets("M00 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute M10
Sheets("M10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute S10
Sheets("S10 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
'Execute S20
Sheets("S20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub
--
H. Frank Situmorang


"Bob Phillips" wrote:

It looks well over-cooked to me, all you need is

Sub AutoShape10_Click()
Sheets("A00").Cells.Copy Sheets("A00 (2)").Cells
Sheets("E00").Cells.Copy Sheets("E00 (2)").Cells
Sheets("S20").Cells.Copy Sheets("S20 (2)").Cells
Sheets("S10").Cells.Copy Sheets("S10 (2)").Cells
Sheets("M10").Cells.Copy Sheets("M10 (2)").Cells
Sheets("M00").Cells.Copy Sheets("M00 (2)").Cells
Sheets("E20").Cells.Copy Sheets("E20 (2)").Cells
Sheets("E10").Cells.Copy Sheets("E10 (2)").Cells
Sheets("SUMMARYWBLA").Select
End Sub

which you might find simpler to adapt.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Hello,

About a year ago, with the help of this forum I was able to make a
macro
to
transfer from an extract sheets ( A00) to and interface sheet ( A00
(2)).

Now there is an additional sheet created by the accounting software
named
E30.

I tried to refresh again my mine, but I almost forgot, can anyone help
me?
the meaning of this VBA:
1.ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Selection.Copy
2. ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
3.ActiveWindow.ScrollWorkbookTabs Sheets:=1
4.Application.CutCopyMode = False

Or is there any way we can slow down the process when we run the macro,
so
that we can see what it does?

This is the VBA which is working but, I do not know how to insert inthe
VBA,
the Sheet E30 extract and E30(20) interface sheets:

Sub AutoShape10_Click()
'
' AutoShape10_Click Macro
' Macro recorded 5/11/2007 by Frank
' This is to copy data from extracts to Interface sheet

'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("A00").Select
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("A00 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("E00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E00 (2)").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("S20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S20 (2)").Select
Cells.Select
ActiveSheet.Paste
Sheets("S10 (2)").Select
Cells.Select
Sheets("S10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("S10 (2)").Select
ActiveSheet.Paste
Sheets("M10 (2)").Select
Cells.Select
Sheets("M10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M10 (2)").Select
ActiveSheet.Paste
Sheets("M00 (2)").Select
Cells.Select
Sheets("M00").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("M00 (2)").Select
ActiveSheet.Paste
Sheets("E20 (2)").Select
Cells.Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("E20").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E20 (2)").Select
ActiveSheet.Paste
Sheets("E10 (2)").Select
Cells.Select
Sheets("E10").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("E10 (2)").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("SUMMARYWBLA").Select
End Sub

--
H. Frank Situmorang





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 65
Default Meaning of some VBA
Bob:

Thanks very much for your explanantion, it really opens my mind and now I
know better about this VBA. I am so amazed with all good poeple like you in
this forum. We, in the developping countiry are really helped by this forum.

Greetings from Jakarta, Indonesia.
--
H. Frank Situmorang


"Bob Phillips" wrote:

Frank,

When you record a macro, the recorder copie all actions, some of which may
be what you did but are not necessary for the job, and also takes a
necessarily singular, simplistic approach to it.

As such, statements like

ActiveWindow.SmallScroll Down:=72

are totally redundant in macros, and can be removed.

It tends to repeat things too, so you may see code like this repeated over
and over, which if needed at all is only neede once at the end

Application.CutCopyMode = False

When you set a value in the recorder, it records it in this manner

Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"

which can be reduced to

Range("N9").FormulaR1C1 = "=+R[1]C[-12]"

or even

Range("N9").Value= "=+R[1]C[-12]"

in other words, no need to select it.

And again, you rarely need to select, so instead of

Range("P9").Select
Selection.Copy

you can use

Range("P9")..Copy

and instead of

Range("Q9:X9").Select
ActiveSheet.Paste

you can use

Range("Q9:X9").Paste

or even better, combine the 4 lines into one, that is

Range("P9").Copy Range("Q9:X9")

which is simpler to read, and more efficient without the selecting.

Taking your code as an example this set, which is repeatable on different
ranges throughout your code

'Execute S20
Sheets("S20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False

can be reduced to

'Execute S20
Sheets("S20 (2)").Select
Range("N9").Value = "=+R[1]C[-12]"
Range("O9").Value = "=+R[2]C[-13]"
Range("P9").Value = "=+R[1]C[-13]"
Range("P9").Copy Range("Q9:X9")
Range("N9:X11").Copy Range("N12:N1004")
Range("N9:X1002").Value = Range("N9:X1002").Value

or even remove the sheet select by using with


'Execute S20
With Sheets("S20 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With

So overall we now have

Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort

'
With Sheets("A00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E00
With Sheets("E00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E10
With Sheets("E10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E20
With Sheets("E20 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute E30
With Sheets("E30 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute M00
With Sheets("M00 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute M10
With Sheets("M10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute S10
With Sheets("S10 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With

'Execute S20
With Sheets("S20 (2)")

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With

Sheets("SUMMARYWBLA").Select

Range("H2").Select
End Sub

But here we see the code is doing the same thing to different sheets, so it
would be better to put this into a separate variable function, like so

Private Function UpdateSheet(ByRef sh As Worksheet)

With sh

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function

Which is called like so

Call UdateSheet(Sheets("S20 (2)"))

The whole code then reduces to

Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort

'
Call UpdateSheet(Sheets("A00 (2)"))
Call UpdateSheet(Sheets("E00 (2)"))
Call UpdateSheet(Sheets("E10 (2)"))
Call UpdateSheet(Sheets("E20 (2)"))
Call UpdateSheet(Sheets("E30 (2)"))
Call UpdateSheet(Sheets("M00 (2)"))
Call UpdateSheet(Sheets("M10 (2)"))
Call UpdateSheet(Sheets("S10 (2)"))
Call UpdateSheet(Sheets("S20 (2)"))

Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub

Private Function UpdateSheet(ByRef sh As Worksheet)

With sh

.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function

which is simpler, more readable, more maintaiabe, and faster.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Frank Situmorang" wrote in message
...
Thanks Bob for your great help. In fact my specialty is an accountant, so
I
am so thankful for your 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
meaning of E in y=3E-.05x^3 Garland Charts and Charting in Excel 2 April 4th 23 12:44 PM
Can anyone tell me the meaning of 'xlsx'? Wayne Excel Discussion (Misc queries) 2 August 3rd 09 06:42 AM
what is the meaning of <? bitwhite Excel Worksheet Functions 3 July 13th 09 04:02 PM
meaning of ######### dick Excel Discussion (Misc queries) 2 January 10th 06 01:28 PM
meaning of Dynamic? mike Excel Programming 4 February 19th 04 03:47 PM


All times are GMT +1. The time now is 12:49 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