Excel Macro 'Sheet.Range.Value' Syntax Questions

[Elliott]

I've got a few very basic syntax questions.
On my main worksheet, I've got a list of sheet names.
I've saved them as variables using the following commands:

Dim ProjectSheet1 As String
Dim ProjectSheet2 As String
ProjectSheet1 = Sheets(1).Range("C6").Value
ProjectSheet1 = Sheets(1).Range("C7").Value

I'd like to extract certain information using data from the "Criteria" sheet
like this:

Sheets("ProjectSheet1").Columns("A:B").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"),
CopyToRange _
:=Columns("A:B"), Unique:=True

This places the information on the active sheet. That's fine, except that
the Sheets("ProjectSheet1") isn't working. If I input the actual sheet name
in quotes, everything runs fine. ProjectSheet1 also doesn't work without
quotes.

 

[PCLIVE]

Try removing the quotes from ProjectSheet1.

Dim ProjectSheet1 As String
Dim ProjectSheet2 As String
ProjectSheet1 = Sheets(1).Range("C6").Value
ProjectSheet1 = Sheets(1).Range("C7").Value

I'd like to extract certain information using data from the "Criteria" sheet
like this:

Sheets(ProjectSheet1).Columns("A:B").AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"),
CopyToRange _
:=Columns("A:B"), Unique:=True



Does that help?

Paul

 

[Elliott]

It doesn't seem to. Maybe it is how i'm declaring the variable Projectsheet1?

Sub VariableDeclarations()

Dim NumberOfProjects As Integer
NumberOfProjects = Sheets(1).Range("C12").Value

Dim ProjectSheet1 As String
Dim ProjectSheet2 As String

ProjectSheet1 = Sheets(1).Range("C6").Value
ProjectSheet2 = Sheets(1).Range("C7").Value

End Sub

 

[Jim Thomlinson]

Projectsheet1 is a string variable that you are populating with the value
that is in cell C6 on whicherver sheet happens to have the index number 1.
Generally speaking that is very dangerous code. You can not guarantee which
sheet will be index number 1. Furthermore if the value in C6 is not a valid
sheet name then your code will crash. What is the name of the sheet where you
want to apply the filter???

 

[Elliott]

OK.
The sheet with index #1 is my main page, which for now i've called "Main."
On it there exists a list of other sheet names. The value in C6 is the name
of the sheet that the pre-filtered data is on. Call it "PreFilteredDataA."
My idea was that if I added another set of pre-filtered data, I could create
the new sheet, PreFilteredDataB, and then copy the macro, except change C6 to
C7.

I've been using easier names here, but here's the real situation: I have 4
pre-filtered data sheets, each with ~50000 entries, and I'll be using macros
to sort them into ~20 categories. The number of data sheets will be
increasing over the next few weeks. I'd like to make the system, (20 sorting
macros) easily expandable when I enter a new data set to filter.
What's the best way to go about things here?