Announcement

Collapse
No announcement yet.

Referencing Active WS w/ Macros Index Match

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Referencing Active WS w/ Macros Index Match

    Hello,

    I'm having trouble referring back to the active sheet name of the newly created sheet. I've tried a lot of different ways like putting in single quote, single quote and double quote and redoing the declarations, but it always comes up as "newsheet" when I look at the function in the cells after I run the macro.

    Could someone help point out what's going on? I changed the text colors to red where I'm having issues.


    Code:
    Dim newsheet As String
    Dim dataname As String
    Dim datasheetname As String
    Dim pivotsheetname As String
    
    dataname = ActiveSheet.ListObjects(1).Name
        datasheetname = ActiveSheet.Name
        pivotsheetname = datasheetname & " Pivot"
        
        Sheets.Add
        newsheet = ActiveSheet.Name
    
    
    
    
        ActiveCell.FormulaR1C1 = "=INDEX(Codes!C7,MATCH('newsheet 'RC[1],Codes!C[-14],0))"
        Range("Q26").Select
        Sheets("Codes").Select
        Columns("G:G").Select
        Sheets(newsheet).Select
        Range("V2").Select
        ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH('newsheet'!RC[1],Codes!C7,0))"
    
    
    
        Range("Z20").Select
        ActiveCell.FormulaR1C1 = ""
        Range("V2").Select
        ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH('newsheet'!RC[1],Codes!C7,0))"
        Range("V2").Select
        ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH('newsheet'!RC[1],Codes!C7,0))"
        Range("V6").Select
        ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH('newsheet'!RC[1],Codes!C7,0))"
        Range("V7").Select
    Last edited by AlanSidman; June 15th, 2017, 09:19.

  • #2
    Re: Referencing Active WS w/ Macros Index Match

    Welcome to the forum. We have a few simple rules we expect all users to accept and abide by. One in particular is to add code tags when VBA code is posted. I have added them for you today. Please re-read the rules you agreed to when you joined and abide by them in the future.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Re: Referencing Active WS w/ Macros Index Match

      Thank you Alan

      Comment

      Working...
      X