Help merging two VBA codes
 

Hi,

I'm having a bit of difficulty merging two different VBA functions into one button.

This the the button code:

Select Code copy to clipboard
Private Sub CommandButton2_Click()

With Sheets("Recommendations Calc").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0).Value = Sheets("Decision Matrix").Range("C2").Value
.Offset(1, 1).Value = Sheets("Decision Matrix").Range("h55").Value
.Offset(1, 4).Value = Sheets("Decision Matrix").Range("I55").Value
deletedup '<--call the macro to delete the dup. and put the last data entry.
End With

End Sub


And this is the other VBA code:


Select Code copy to clipboard
Sub thebigcopy()
'
' thebigcopy Macro
'

'
ActiveWindow.SmallScroll Down:=-21
Columns("A:B").Select
Selection.Copy
Sheets("Recommendations").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Recommendations Calc").Select
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Range("C37").Select
Sheets("Recommendations").Select
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub



I want to merge the 2nd code into the 1st button VB code

Can anyone help me?

Thank You!!

Help merging two VBA codes
 

Hi arceaf,

The first code you have provided is a vba script and it is attached to an activex button and the second code is a macro recorded from excel.

You could do something like below, just copy paste the recorded macro into the script but this is just an example as it will not work like that out of the box.

You will need to specify from what worksheet you are running your code because it seems there are a recommandation sheet from where the script is taking the data and there is "decision matrix" sheet from where the data is copied. It seems the recorded macro is triggered from the decision matrix sheet. Can you confirm? If so, you need to choose from which sheet you want your button to run the code or tell on which sheet sheet the button is so to consider it the destination sheet and the other sheet, the source data sheet..

Select Code copy to clipboard
Private Sub CommandButton2_Click()

With Sheets("Recommendations Calc").Range("A" & Rows.Count).End(xlUp)
Offset(1, 0).Value = Sheets("Decision Matrix").Range("C2").Value
Offset(1, 1).Value = Sheets("Decision Matrix").Range("h55").Value
Offset(1, 4).Value = Sheets("Decision Matrix").Range("I55").Value
deletedup
'<--call the macro to delete the dup. and put the last data entry.

ActiveWindow.SmallScroll Down:=-21
Columns("A:B").Select
Selection.Copy
Sheets("Recommendations").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Recommendations Calc").Select
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Range("C37").Select
Sheets("Recommendations").Select
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


End With

End Sub

This is just to show you could copy paste the code this way but it won't work until you tell which sheet is the source data and which sheet is the destination.

Pascal
http://www.multiskillz.elementfx.com/

Help merging two VBA codes
 

"arceaf" wrote:
I'm having a bit of difficulty merging two different
VBA functions into one button.

Please explain the "difficulty". I presume you already tried copying and
pasting the text of second macro into the text of the first macro. In what
way does that not do what you expect?


"arceaf" wrote:
This the the button code:
[....]
And this is the other VBA code:

Why "merge" the code at all? Why not simply call the second macro at the
end of the first macro? See below. In what way does that not do what you
expect?

Private Sub CommandButton2_Click()
With Sheets("Recommendations Calc").Range("A" & Rows.Count).End(xlUp)
..Offset(1, 0).Value = Sheets("Decision Matrix").Range("C2").Value
..Offset(1, 1).Value = Sheets("Decision Matrix").Range("h55").Value
..Offset(1, 4).Value = Sheets("Decision Matrix").Range("I55").Value
'<--call the macro to delete the dup. and put the last data entry.
deletedup
End With
thebigcopy ' <-- added
End Sub

(Aside: I would put the call to "deletedup" after End With.)

PS: "thebigcopy" changes the active worksheet. It might be prudent to
modify it so that it goes back to the original active worksheet. To wit:

Sub thebigcopy()
Dim oldWS as Worksheet
Set oldWS = Activesheet ' <-- remember initial active worksheet
[....]
oldWS.Activate ' <-- restore initial active worksheet
End Sub