How to run macro from active sheet

[timcar1]

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."

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

 

[Datsmart]

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.

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.

 

[timcar1]

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.

 

[Datsmart]

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.

 

[timcar1]

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?

 

[Datsmart]

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:

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

 

[timcar1]

Hi John,

Thanks for all your help. I really appreciate it.

Tim

 

[Bmeyer]

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

 

[Datsmart]

Hi Bmeyer,
Welcome to the board!

This code will copy the range you listed in your code to the active worksheet:

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.

 

[Bmeyer]

Thanks John

your code worked 1st time. I will ensure to create my own threads!

Barry