Using Variables to generate a line of Code

  • I have a workbook that has a varying number of sheets in it. I have a loop to put all the worksheet names into a string. The problem comes when I put the variable into a line of code I get runtime error 9, subscript out of range.
    I use MsgBox (txt2) and it has the correct syntax that I am looking for, not sure why I'm getting the error

  • I have used strings for sheet names but I have not tried pulling them out of an array like that. I am curious if something besides just the name is ending up in txt2. In this oversimplified code below, if I enter debug mode and hover over sSheetName in the with line, I see what is in the picture below. If you do that for txt2 in the problem line, are you seeing anything else in there other than the actual sheet name, like extra " or anything else?


  • You can't create an array by using the literal string "Array". Here is another approach.

  • I am having a similar situation when I use the Same sheets to create the Source Data for a pivot table. I try to use a variable to replace all the information needed for SourceData, If I use MsgBox it comes out just right, when I insert the variable it gives me an error. Same as before. How would I handle this?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!