Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Referencing Active WS w/ Macros Index Match

  1. #1
    Join Date
    15th June 2017
    Posts
    2

    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.


    VB:
    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([COLOR=#ff0000]'newsheet[/COLOR] '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('[COLOR=#ff0000]newsheet'[/COLOR]!RC[1],Codes!C7,0))" 
     
     
     
    Range("Z20").Select 
    ActiveCell.FormulaR1C1 = "" 
    Range("V2").Select 
    ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH([COLOR=#ff0000]'newsheet'[/COLOR]!RC[1],Codes!C7,0))" 
    Range("V2").Select 
    ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH('[COLOR=#ff0000]newsheet'![/COLOR]RC[1],Codes!C7,0))" 
    Range("V6").Select 
    ActiveCell.FormulaR1C1 = "=INDEX(Codes!C8,MATCH([COLOR=#ff0000]'newsheet'[/COLOR]!RC[1],Codes!C7,0))" 
    Range("V7").Select 
    
    
    Last edited by AlanSidman; 1 Week Ago at 09:19.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2011
    Location
    Steamboat Springs
    Posts
    755

    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 their reputation button (*) below their name.

  3. #3
    Join Date
    15th June 2017
    Posts
    2

    Re: Referencing Active WS w/ Macros Index Match

    Thank you Alan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 15
    Last Post: April 25th, 2015, 19:23
  2. Replies: 6
    Last Post: July 13th, 2013, 04:18
  3. Replies: 2
    Last Post: July 21st, 2012, 00:28
  4. VBA To Place Index/Match Function In Active Cell
    By bkopacz in forum Excel General
    Replies: 2
    Last Post: June 23rd, 2010, 10:19
  5. Index Small Referencing
    By StephenR in forum Excel General
    Replies: 8
    Last Post: December 11th, 2006, 20:37

Bookmarks

Posting Permissions

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