Macro problem! NEED HELP!!!
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Macro problem! NEED HELP!!!

  1. #1
    Guest

    Default

     
    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

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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:
    Code:
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com