need VBA code to use non-specific references

**QMT** · 09-21-2021, 06:44 PM

I have a code that uses two columns of a .csv file and creates a new Excel worksheet (WS) with a graph from those two columns. It worked well when all the files (hundreds) had the same name for the single WS in the workbook (WB).

I need to use this code on a new set of files and this time each WS (only one exists in ea WB) is name of the file, and they are all different obviously. What I need to do is change all references to the previous WS name, in this case, "Fall", to something that will accept ANY name as the WS name.

Also, this time I need to save each file from a .csv to a .xlsx, while retaining the current file name for that file.

This is part of the code:

Please Login or Register  to view this content.

Would I change the .Name to something else? I just don't know how to begin with this. Thanks. I don't think I need to add the files I'm using for this, but if samples would help, please let me know.

**TMS** · 09-21-2021, 07:28 PM

Maybe, try something like:

Please Login or Register  to view this content.

**QMT** · 09-21-2021, 08:11 PM

Thank you. I will try that, but before I do, would it be the same for the line (above)

Please Login or Register  to view this content.

Would I put

Please Login or Register  to view this content.

or do I need to include the extra "=" as well?

Or something else?

I had tried using "Sheet1", but that didn't work.

**QMT** · 09-21-2021, 08:34 PM

So, the first problem I'm having is that I can't actually put "shName = "Fall"" at the Dim declaration (is that what you intended?) because there is no sheet name called "Fall" in these files.
I also can't use

Please Login or Register  to view this content.

I get runtime error 1004, Parameter not valid.

**TMS** · 09-22-2021, 02:11 AM

This one

Please Login or Register  to view this content.

would be

Please Login or Register  to view this content.

Just trying to show how to change the hard coded sheet name to a variable so you only have to change one thing. Where your code gets the content of that variable is up to you … presumably when you open each .csv file.

**QMT** · 09-23-2021, 07:33 PM

I guess I didn't explain clearly. I can not use a specific reference to the sheet's name (such as shName = "Fall") because every file has a new sheet name, named for the Workbook. They are all different. I used your first example *exactly*, with the exception of " shName = "Fall"" because the sheet name does not equal "Fall". But when I put:

Please Login or Register  to view this content.

I get runtime error 1004, Parameter not valid. I have included a small sample WS and a sample of the code.

The current code in the sample is another attempt to have the name reference a variable WS name, but it isn't working either. Here's another thought: Is this line

Please Login or Register  to view this content.

referencing the A2 cell on the page the chart is on (which has nothing in that cell) or the original/first sheet, which, from what I've read, it should recognize as Sheet1?

I have now posted this question elsewhere as well. I don't understand why that is frowned upon (yes I did read the FAQ) because I'm only trying to get an answer. I hope it's not a problem and I wanted to be upfront about it. Thanks.

This is where the question was asked elsewhere: stackoverflow dot com/questions/69309185/vba-excel-reference-variably-named-worksheets-for-chart-series/69309371#69309371

**QMT** · 09-29-2021, 08:44 PM

The problem has been solved. I had not added a line "ActiveChart.FullSeriesCollection(1)" before the first "Sheetname" line. After that, these lines worked:

Please Login or Register  to view this content.

need VBA code to use non-specific references

LinkBack

Thread Tools

Rate This Thread

Display

need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Re: need VBA code to use non-specific references

Thread Information

Users Browsing this Thread

Similar Threads

Return specific value that applies to duplicate references

[SOLVED] Is it possible to reference a specific cell or row using structured references in a table?

Macro-Relative References Using Specific Cell Info

[SOLVED] How to fully qualify all Cells() references with a specific worksheet

How do I set up a Custom Header that references a specific cell

[SOLVED] Working with non-specific cell references

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions