Thread: Meaning of some VBA
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Frank Situmorang[_2_] Frank Situmorang[_2_] is offline
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