How to run macro from active sheet

timcar1

New Member
Joined
Mar 17, 2009
Messages
22
Hello,

I have written the below macro to copy data from a sheet called "Fixit Example 1" to a sheet called "Fixit Summary Example". It works fine, but I'd like to be able to run it from any "Fixit Example XX" worksheet. As written, the data is always copied from "Fixit Example 1" regardless of what worksheet I have open.
I tried replacing
Windows("Fixit Example 1.xls").Activate
with
Windows(Active.Worksheet).Activate

but got an error "Run time error 424. Object required."



Code:
Sub MoveData()
'
' MoveData Macro
' Macro recorded 18/03/2009
'
' Keyboard Shortcut: Ctrl+m
'
Windows("Fixit Summary Example.xls").Activate
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Windows("Fixit Example 1.xls").Activate
Range("A2").Select
Selection.Copy
Windows("Fixit Summary Example.xls").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("Fixit Example 1.xls").Activate
Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Fixit Summary Example.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Windows("Fixit Example 1.xls").Activate
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Fixit Summary Example.xls").Activate
Range("C2").Select
ActiveSheet.Paste
Windows("Fixit Example 1.xls").Activate
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Fixit Summary Example.xls").Activate
Range("D2").Select
ActiveSheet.Paste
Range("E5").Select
End Sub
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi timcar1,

Welcome to the board.


I took your recorded code and removed all the "Activates" and "Selects" that the recorder puts in. You will note that the code runs instantly instead of jumping back and forth.
Code:
Sub MoveData()
' Macro recorded 18/03/2009
' Keyboard Shortcut: Ctrl+m
'Insert new Row
Workbooks("Fixit Summary Example.xls").Sheets("Sheet1").Rows("2:2").Insert Shift:=xlDown
'Copy Data
Range("A2").Copy Workbooks("Fixit Summary Example.xls").Sheets("Sheet1").Range("A2")
Range("A5").Copy Workbooks("Fixit Summary Example.xls").Sheets("Sheet1").Range("B2")
Range("C2").Copy Workbooks("Fixit Summary Example.xls").Sheets("Sheet1").Range("C2")
Range("C5").Copy Workbooks("Fixit Summary Example.xls").Sheets("Sheet1").Range("D2")
End Sub
This code assumes both workbooks are open and that the one you want to copy FROM is the active workbook with the proper worksheet active.

Note: I included the worksheet name "Sheet1" as the sheet to copy to. You did not state what worksheet to copy to. Adjust the worksheet name as needed.
 
Upvote 0
Hi John,

Thanks a lot. That works great.

I'm curious though. After seeing what you did, as a test I modified my code to just comment out the four lines referring to Fixit Example 1, but this did not work. It now copied the four fields from Fixit Example Summary into Fixit Example Summary.
Do you know why your code ignores the Summary sheet and assumes it should copy from the active worksheet, and mine would look to the Summary sheet?

Thanks.
 
Upvote 0
Excel will copy from the active worksheet unless explicitly instructed to copy from another worksheet. The line, "Range("A2").Copy" will copy from whatever workbook and worksheet is the active one.
Your first few lines of code activate the "Fixit Summary Example.xls" workbook to insert a new row. That workbook and worksheet will remain active if you comment out the lines that "Activate" something else.
 
Upvote 0
Hi John,

Everything you said makes sense, and that is how I hoped it would work. I don't know VB, but comparing the programs it appears my recorded macro specifically activates the Summary sheet before the insert and pastes, whereas yours manages to insert the new line and paste to the Summary sheet without "activating" it.
Since I'd like to avoid writing VB code every time I need to so something like this, do you know if there is a way to record the macro without activating the Summary sheet?
 
Upvote 0
Sorry, there is no shortcut to improve on what the macro Recorder puts together.

The beauty of VBA code is that it can do things that you can not do using the keyboard or the mouse, like copying data without "Selecting" it. When you record a macro it has to follow each step you take, which has to include "Selecting" cells and sheets.

Another way of writing better code is to use "variables".
Here is an example of your code using some variables:
Code:
Sub MoveData2()
' Macro recorded 18/03/2009
' Keyboard Shortcut: Ctrl+m
'Assign Variables for Target Workbook and Target Worksheet
TargetWB = Workbooks("Fixit Summary Example.xls").Name
TargetWS = Sheets("Sheet1").Name

'Insert new Row
Workbooks(TargetWB).Sheets(TargetWS).Rows("2:2").Insert Shift:=xlDown
'Copy Data
Range("A2").Copy Workbooks(TargetWB).Sheets(TargetWS).Range("A2")
Range("A5").Copy Workbooks(TargetWB).Sheets(TargetWS).Range("B2")
Range("C2").Copy Workbooks(TargetWB).Sheets(TargetWS).Range("C2")
Range("C5").Copy Workbooks(TargetWB).Sheets(TargetWS).Range("D2")
End Sub
 
Upvote 0
Dear John

I am veery new to VBA, I have a question hopefully you could help?

I am trying to copy a range from a Sample worksheet range, and then pasting that range into the active worksheet(lets say "Sheet 1") and keeping the cursor on the active worksheet(any active worksheet) on the active cell,

But I would like to use the macro on all my worksheets, thus the range from the "sample" sheet should copy into sheet 1 or sheet 2 without constantly modifying Macro.

my code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-03-19 by User
'
' Keyboard Shortcut: Ctrl+t
'
Range(ActiveCell.Address).Name = "StartCell"
Sheets("Sheet2").Select
Range("A1:E3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Selection.Insert Shift:=xlDown
Range("A4").Select
Application.Goto "StartCell"
End Sub

please advise
 
Upvote 0
Hi Bmeyer,
Welcome to the board!

This code will copy the range you listed in your code to the active worksheet:
Code:
Sub InsertData()
'Assign variable to active worksheet
    Var1 = ActiveSheet.Name
'Insert three new rows
    Sheets(Var1).Range("A1:E3").Insert Shift:=xlDown
'Copy Range from Sample worksheet
    Sheets("Sample").Range("A1:E3").Copy Sheets(Var1).Range("A1")
End Sub
This will always copy Range A1:E3 from worksheet "Sample" to the active worksheet, be it Sheet1, Sheet2, or any other worksheet you have active. The code also inserts three rows in columns A thru E as you did in your recorded code.

Since the code does not "Select" anything, there is no need to "keep the cursor on the active worksheet", etc.

By the way, in the future I would recommend opening a new thread for your questions rather than using someone else's post. That way it is more likely others will review it and you can get a more timely response.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top