Excel Macro 'Sheet.Range.Value' Syntax Questions
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").AdvancedFil ter 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. |
Excel Macro 'Sheet.Range.Value' Syntax Questions
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").AdvancedFilte r Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True Does that help? Paul -- "Elliott" wrote in message ... 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").AdvancedFil ter 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. |
Excel Macro 'Sheet.Range.Value' Syntax Questions
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 "PCLIVE" wrote: 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").AdvancedFilte r Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True Does that help? Paul -- "Elliott" wrote in message ... 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").AdvancedFil ter 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. |
Excel Macro 'Sheet.Range.Value' Syntax Questions
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??? -- HTH... Jim Thomlinson "Elliott" wrote: 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 "PCLIVE" wrote: 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").AdvancedFilte r Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True Does that help? Paul -- "Elliott" wrote in message ... 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").AdvancedFil ter 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. |
Excel Macro 'Sheet.Range.Value' Syntax Questions
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? "Jim Thomlinson" wrote: 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??? -- HTH... Jim Thomlinson "Elliott" wrote: 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 "PCLIVE" wrote: 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").AdvancedFilte r Action:= _ xlFilterCopy, CriteriaRange:=Sheets("Criteria").Range("A2:A3"), CopyToRange _ :=Columns("A:B"), Unique:=True Does that help? Paul -- "Elliott" wrote in message ... 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").AdvancedFil ter 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. |
All times are GMT +1. The time now is 03:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com