Good day all - thanks in advance for any assistance you can provide.
I'm working on a rather large workbook that has around 20 different sheets, with one master data sheet. What I'm trying to accomplish is to have a VB code that based upon 2 input cells will filter the data. I have already gotten the filtering code to work without any trouble. My input cells are M3 & M4.
With Worksheets("Data")
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("A8").AutoFilter Field:=8, Criteria1:=Array("=" & .Range("M3").Value)
.Range("A8").AutoFilter Field:=6, Criteria1:=Array("<" & .Range("M4").Value)
Once data is filtered it will then copy/paste to one of the 20 sheets that exist in this workbook. The part I'm struggling with is where to paste the data (columns A:H on the sheet called "Data"). On the "Data" sheet I've created a dropdown box (cell M5) that has all the existing SheetNames for the entire workbook listed by utilizing the following formula on a (=IFERROR(INDEX(SheetNames,R2),""), =IFERROR(INDEX(SheetNames,R3),""), etc). on a look-up reference sheet. This works great and all the sheets are listed w/ the exact name.
What I'd like to do is set a "TARGET" in the code for the location where the copied data can be pasted to based upon the value that exists in cell "M5".
I've done a lot of research over the last few days, and I'm struggling to come up with something.
Dim SOURCE As Worksheet
Dim TARGET As Worksheet
Set SOURCE = ThisWorkbook.Worksheets("Data")
Set TARGET =
SOURCE.Range("A:H").Copy Destination:=TARGET.Range("A1")
I know what I have here probably isn't close to what is needed, any insights you can provide would be greatly appreciated.
-Jay
I'm working on a rather large workbook that has around 20 different sheets, with one master data sheet. What I'm trying to accomplish is to have a VB code that based upon 2 input cells will filter the data. I have already gotten the filtering code to work without any trouble. My input cells are M3 & M4.
With Worksheets("Data")
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("A8").AutoFilter Field:=8, Criteria1:=Array("=" & .Range("M3").Value)
.Range("A8").AutoFilter Field:=6, Criteria1:=Array("<" & .Range("M4").Value)
Once data is filtered it will then copy/paste to one of the 20 sheets that exist in this workbook. The part I'm struggling with is where to paste the data (columns A:H on the sheet called "Data"). On the "Data" sheet I've created a dropdown box (cell M5) that has all the existing SheetNames for the entire workbook listed by utilizing the following formula on a (=IFERROR(INDEX(SheetNames,R2),""), =IFERROR(INDEX(SheetNames,R3),""), etc). on a look-up reference sheet. This works great and all the sheets are listed w/ the exact name.
What I'd like to do is set a "TARGET" in the code for the location where the copied data can be pasted to based upon the value that exists in cell "M5".
I've done a lot of research over the last few days, and I'm struggling to come up with something.
Dim SOURCE As Worksheet
Dim TARGET As Worksheet
Set SOURCE = ThisWorkbook.Worksheets("Data")
Set TARGET =
SOURCE.Range("A:H").Copy Destination:=TARGET.Range("A1")
I know what I have here probably isn't close to what is needed, any insights you can provide would be greatly appreciated.
-Jay