Combining two separate macros into one [Solved/Closed]

kat - Jun 16, 2010 at 02:52 PM - Latest reply:  Devarajan
- Jul 23, 2013 at 12:40 PM
Hello,

I currently am using these two separate macros independently but would like to combine them into one with the archive one being first. I'm pasting my code below. Can anyone help?

1st macro:

Sub archive()
'
' archive Macro
' Macro recorded 4/19/2010 by tklb051
'
Rows("3:300").Select
Selection.Copy
Sheets("Forms Archive").Select

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveSheet.Paste
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A2:cb2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:cb65536").Select
Range(Selection, Selection.End(xlUp)).Select
Range("A2:cb300").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
Range("B2").Select

End Sub




2nd macro:

Sub CopyColumn()

' Update Master
'*************************************************************************
' Turn off Screen Updating
'*************************************************************************
Application.ScreenUpdating = False
' This is if you want a pop-up message before running the job
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to update the 'Master'?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Conservation Warning" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.

ShMaster.Range("A3").Select ' puts the "cursor" in the first spot to begin the cop

Do ' begin loop

If ActiveCell.Value <> vbNullString Then ' check if the cell is empty
If ActiveCell.EntireRow.Hidden = False Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Value
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row > 500

ShMaster.Range("c3").Select ' puts the "cursor" in the first spot to begin the cop

Do ' begin loop

If ActiveCell.Value <> vbNullString Then ' check if the cell is empty
If ActiveCell.EntireRow.Hidden = False Then
ActiveCell.Offset(0, 1).Value = ActiveCell.Value
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row > 500
ShMaster.Range("g3").Select ' puts the "cursor" in the first spot to begin the cop

End Sub
See more 

8 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2010 at 06:32 PM
0
Thank you
Most easy way would be create a third one


Sub combo()

call archive()
call CopyColumn()

End Sub
I cannot get this to work. It keeps erroring out on ------>

ShMaster.Range("A3").Select
(in second macro)

However, the second macro still works if I run it by itself.....just not with the combo. Thanks for your help!!!!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 08:50 AM
Could you please upload a sample file with SAMPLE DATA AND MACRO etc on some shared site like http://www.speedyshare.com/ , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding
I used speedyshare. Here is the link:
http://www.speedyshare.com/files/23012564/Sample_1.xls

I appreciate your help. thank you!
Lot of Thanks for you
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 18, 2010 at 05:13 AM
0
Thank you
Add

ShMaster.Select

before the line ShMaster.Range("A3").Select


It is unable to select the range as activesheet is Forms Archive at this point.
You are awesome! Thank you so much for taking the time to help me. It works great!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 19, 2010 at 06:44 AM
you certainly are very welcome.