PDA

View Full Version : Combine Macros



RINCONPAUL
01-04-2016, 01:17 PM
In the attachment, I'm looking to turn 18 macro button presses into one! Appreciate your help.

The basics: on sheet 'Rule1' I have pasted some data from elsewhere. There is a macro button on this sheet called 'Pass1'. I press that, the macro takes a random 60% of that data and pastes it to 'Sheet1'. I then go to sheet 'Results' and press button 'Using a pivot table'. A pivot table of the data on sheet 1 is generated. (Bear in mind that this can take a few seconds with big data sets?). Once the pivot table is generated I press button 'Capture1' This transfers positive results from the pivot table elsewhere on the same sheet, clears the pivot table.

Now, I repeat all the moves again except press 'Capture2'. Repeat again, press 'Capture3'., repeat...,repeat...,repeat again, press 'Capture6'. (very time consuming and prone to error as you get bored).

All the pivot table data captures are stacked in tables V1 ~ V6 (Y47 down). A table at the top of the sheet sums all the cells that have a positive number in all 6 - V tables. I assess this information and decide on a filter which I apply to the original data and then paste that data into sheet 'Rule2'. I then press button 'Pass2' and start the whole repetitive exercise over again.

Ideally, once I have pasted some data into any one of the 'Rule?' sheets, a macro button on that sheet, should enact the 18 button presses that currently follow?

Problem I've got is the file I want to attach, even when stripped of any data and zipped, is 1.7mb? Never realised vba code took up so much memory. Any suggestions?

Cheers

SamT
01-04-2016, 01:36 PM
You can export the code modules, which saves them as *.bas files (text,) then zip them.

Try using the free 7Zip program. It seems to have a high compression ratio.

Save the workbook as an *.xlsb file and see what the size is.

RINCONPAUL
01-04-2016, 02:00 PM
Sam,
Just snuck it under the wire 1060Mb.

jonh
01-04-2016, 02:16 PM
Create a new sub (macro) that calls all of your other subs.



sub RunAllMacros()
pass1
Macro10
Capture1
'etc ...
end sub


Then link that to your button.

RINCONPAUL
01-04-2016, 02:16 PM
I think I solved it?


Sub Main1()
Call Pass1
Call Macro10
Call Capture1
Call Pass1
Call Macro10
Call Capture2
Call Pass1
Call Macro10
Call Capture3
Call Pass1
Call Macro10
Call Capture4
Call Pass1
Call Macro10
Call Capture5
Call Pass1
Call Macro10
Call Capture6

End Sub

RINCONPAUL
01-04-2016, 02:18 PM
Thanks jonh, you just beat me to it. Funny how complex thoughts, can be so simple to fix sometimes?
Cheers

SamT
01-04-2016, 05:16 PM
Ya might need some DoEvents in there

YOu might try

For i = 1 to 6
Pass1
Macro10
DoEvents
Application.Run "Capture" & i
Next

RINCONPAUL
01-05-2016, 08:09 PM
Sam, I'm wondering if you could rewrite the code somehow? You see, at the moment macro Capture1...6 are written separately. If I wanted to run 20 or 50 or? number of Capture macros, the only difference between them is rows where to paste too. I've shown capture 5 & 6 code here, the only difference is each Capture pastes 42 rows further down the sheet. In light of your suggestion above, if we could simply have one macro called Capture a starting point (Y45) and nominate how many times to carry out the copy paste adding 42 rows to the range each time? :


Sub Capture5()
'
' Capture5 Macro
'
'
Range("E45:W80").Select
Selection.Copy
Range("Y213").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E3:W38").Select
Application.CutCopyMode = False
Selection.ClearContents

End Sub
Sub Capture6()
'
' Capture6 Macro
'
'
Range("E45:W80").Select
Selection.Copy
Range("Y255").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E3:W38").Select
Application.CutCopyMode = False
Selection.ClearContents

End Sub

jonh
01-06-2016, 03:17 AM
Sub main()
Const iMin As Byte = 1, iMax As Byte = 100
Const k As Byte = 45, l As Byte = 42
Dim i As Long, j As Variant

j = InputBox("enter number of iterations (" & iMin & "-" & iMax & ")", , 1)
If Not IsNumeric(j) Then Exit Sub
If j < iMin Or j > iMax Then Exit Sub


For i = 1 To j
pass1
Macro10
Capture "Y" & k + ((i - 1) * l)
Next
End Sub


Sub Capture(s As String)
Range("E45:W80").Copy
Range(s).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("E3:W38").ClearContents
End Sub

RINCONPAUL
01-06-2016, 10:23 AM
What a "pearler" jonh. That's Aussie slang for GREAT!!

Thanks so much, you've made my day, enjoy yours too.

snb
01-06-2016, 10:35 AM
You made separate macros that you want to combine later.
So you'd better had not split them in the first place.
You can use the code in those separate macros in only 1 macro if you use a simple for... next loop.


e.g. to combine your 'capture5' and 'capture6'


Sub M_snb()
for j=1 to 2
cells(choose(j,213,255),25).resize(35,18)=cells(45,5).resize(35,18).value
Range("E3:W38").ClearContents
next
end sub

But still it's 'clumsy coding', because why using the same ranges over and over again ?

SamT
01-06-2016, 10:38 AM
Copy 36 Rows and Paste every 42 rows. Offset = Paste# - Copy#. Adjust for my math

Sub Capture()
Range("E45:W80").Copy
Cells(Rows.Count, 26).End(xlUp).Offset(6).PasteSpecial xlPasteValues
Range("E3:W38").ClearContents
Application.CutCopyMode = False
End Sub

Slightly more verbose, but easier to maintain

Sub Capture()
Const EmptyRows As Long = 6

Range("E45:W80").Copy
Cells(Rows.Count, 26).End(xlUp).Offset(EmptyRows).PasteSpecial xlPasteValues
Range("E3:W38").ClearContents
Application.CutCopyMode = False
End Sub

Lastly: The very verbose way I would do it.
'At the top of the code page
Const RandomDataRange As String = "E45:W80"
Const SeparatorRows As Long = 6
Const DataPasteColumn As Long = 26 'Column Y

Sub Capture()
Range(RandomDataRange).Copy
Cells(Rows.Count, DataPasteColumn).End(xlUp).Offset(SeparatorRows).PasteSpecial xlPasteValues
Range(RandomDataRange).ClearContents
Application.CutCopyMode = False
End Sub

The way I do the Procedure is that it has no hard coded values(locations) and it tells you in pretty plain language exactly what it's doing.

RINCONPAUL
01-06-2016, 10:45 AM
Thanks snb & SamT for your contributions, much appreciated :)