Running Macros in Seperate Workbooks

[stillenacht]

I am having a problem running a macro I originally created in one workook in other workbooks. The code is a simple copy paste, remove duplicates, and sort alphabetically. However, I want to be able to run it without the original workbook open. I put it in VBA Project (PERSONAL.XLSB), but when I attempt to run it in the "Macros" menu of another workbook, it outputs:

Run-time error '424':
Object Required

How do I get it to recognize it should use the code on my new workbook automatically?

Here is the code:

Sub ArrangePrices()


'For formatting realtimedata into readable format


'Copy each sheet to first sheet
Sheet2.Range("A1:B2000").Copy
Sheets("Sheet1").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False




Sheet3.Range("B1:B2000").Copy
Sheets("Sheet1").Activate
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheet4.Range("B1:B2000").Copy
Sheets("Sheet1").Activate
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheet5.Range("B1:B2000").Copy
Sheets("Sheet1").Activate
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheet6.Range("B1:B2000").Copy
Sheets("Sheet1").Activate
Range("F1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheet7.Range("B1:B2000").Copy
Sheets("Sheet1").Activate
Range("G1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


'Insert Headers


[B1].Value = 1000
[C1].Value = 1100
[D1].Value = 1200
[E1].Value = 1300
[F1].Value = 1400
[G1].Value = 1500


'Remove Duplicates


ActiveSheet.Range("A1:G2000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes


'Sort Alphabetically
With Sheets("Sheet1")
.Range("A2:G2000").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

 

[jproffer]

Just have to tell it you want to do that to the activeworkbook...try this out on a COPY of your workbook ...it's not "undoable"...

Sub ArrangePrices()
Dim i As Integer

'For formatting realtimedata into readable format


'Copy each sheet to first sheet
With ActiveWorkbook
    .Sheet2.Range("A1:B2000").Copy .Sheets("Sheet1").Range("A1")
    .Sheet3.Range("B1:B2000").Copy .Sheets("Sheet1").Range("C1")
    .Sheet4.Range("B1:B2000").Copy .Sheets("Sheet1").Range("D1")
    .Sheet5.Range("B1:B2000").Copy .Sheets("Sheet1").Range("E1")
    .Sheet6.Range("B1:B2000").Copy .Sheets("Sheet1").Range("F1")
    .Sheet7.Range("B1:B2000").Copy .Sheets("Sheet1").Range("G1")
End With
'Insert Headers

For i = 2 To 7
    Cells(1, i).Value = 800 + (100 * i)
Next i
    
'[B1].Value = 1000
'[C1].Value = 1100
'[D1].Value = 1200
'[E1].Value = 1300
'[F1].Value = 1400
'[G1].Value = 1500


'Remove Duplicates


ActiveWorkbook.ActiveSheet.Range("A1:G2000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes


'Sort Alphabetically
With ActiveWorkbook.Sheets("Sheet1")
    .Range("A2:G2000").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
End Sub

I left in some of the code in case my way didn't work (such as entering your headers). It's commented out, so you can delete it as long as the code does what you want.

 

[stillenacht]

Thanks for the answer! I'm a complete noob haha.

 

[jproffer]

You're quite welcome. Hopefully it worked for you....?? If not, don't hesitate to come back. There are a lot of talented people here...and we were all new to this at one time or another.