Macro independant of workbook

[nerak87]

Hi Guys,

My problem: I have written a macro to do some data crunching. It refers to a set of three rows at the bottom of the sheet named 'Calculation'. I developed this macro in a test workbook containing a sample of the data I need to work on. I made the macro into a button on the toolbar.

Now, when I try to run this macro in the actual data workbooks it opens up the test workbook, and if I move the test workbook it won't work at all.

How can I make the macro independant of the original workbook where it was created?

I'm an interested and curious but not terribly knowledgable programmer.

For info here is the macro:

Sub copyandpaste()
Dim Rng As Range
Dim Sht As Worksheet
Dim SrcRow As Range
Set Sht = ActiveSheet
Set Rng = ActiveCell.EntireRow
Set SrcRow = Range("Calculation").EntireRow
Rng.Insert Shift:=xlDown
Rng.Insert Shift:=xlDown
Rng.Insert Shift:=xlDown
SrcRow.Copy Rng.Offset(-3, 0)
'Set Rng = Rng.Offset(1, 0)
Rng.Select
End Sub

Thanks in advance for your suggestions.

Nerak

 

[venkat1926]

try parking the macro in the personal.xls workbook. whenever any excel workbook is opened the personal.xls workbook is also opens but is hidden.

 

[nerak87]

Thanks. I managed to find about about personal.xls and have done that. Problem solved.