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