Macro problem! NEED HELP!!!

[Guest]

Dear Mr. Excel,

I have a problem with a macro I have written.

Sub Start()
Sheets(1).Cells(3, 1).CurrentRegion.Copy
Sheets(2).Cells(6, 1).PasteSpecial xlValues
Cells(6, 3).FormulaR1C1 = "=SUMIF(Kelder!C,RC[-2],Kelder!C[2])"
Cells(6, 3).Copy
m = ActiveSheet.UsedRange.Rows.Count
For x = 6 To m
Cells(x, 3).PasteSpecial
Next
Application.CutCopyMode = False
End Sub

The formula contains the word “Kelder” because that’s the name of the active sheet.
If I change the name of the sheet, the macro doesn’t work anymore.
The name of the sheet varies.
How can I create a macro who works regardless the name of the sheet

 

[Barrie Davidson]

On 2002-02-18 05:54, Anonymous wrote:
Dear Mr. Excel,

I have a problem with a macro I have written.

Sub Start()
Sheets(1).Cells(3, 1).CurrentRegion.Copy
Sheets(2).Cells(6, 1).PasteSpecial xlValues
Cells(6, 3).FormulaR1C1 = "=SUMIF(Kelder!C,RC[-2],Kelder!C[2])"
Cells(6, 3).Copy
m = ActiveSheet.UsedRange.Rows.Count
For x = 6 To m
Cells(x, 3).PasteSpecial
Next
Application.CutCopyMode = False
End Sub

The formula contains the word “Kelder” because that’s the name of the active sheet.
If I change the name of the sheet, the macro doesn’t work anymore.
The name of the sheet varies.
How can I create a macro who works regardless the name of the sheet

Is the worksheet always in the same position (i.e., it's always the first worksheet)? If so, you could use:

Sub Start()
Dim SheetName As String

Sheets(1).Cells(3, 1).CurrentRegion.Copy
SheetName = Sheets(1).Name
Sheets(2).Cells(6, 1).PasteSpecial xlValues
Cells(6, 3).FormulaR1C1 = "=SUMIF(" & SheetName & "!C,RC[-2]," & _
    SheetName & "!C[2])"
Cells(6, 3).Copy
m = ActiveSheet.UsedRange.Rows.Count
For x = 6 To m
Cells(x, 3).PasteSpecial
Next
Application.CutCopyMode = False
End Sub

PS - you should post this question in the Macro Forum

_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-02-18 05:59