Filtering/Copying a table via VBA.

[frobeniusabnorm]

Hello folks! I have a conundrum and I've not yet been able to successfully sift through the google results for a potential solution.

Have a worksheet with a bunch of client data that field crew use by entering a unique identifier and sending the document to that client. The main data tab stays hidden. Trying to make it so that all of the desired tabs are copied over away from that main report into a personalized one for that client. Mostly done that easily enough (code below) except I have a few formulas that rely on a dynamic month drop-down that uses that clients data specifically from the data tab.

So the question is: Is there a way I can add to my code so that it takes that unique identifier and copies ONLY the rows with that identifier from the main table over to the new document?

Sub copysheets()
Dim wb As Workbook

Worksheets("Report Tab3").Visible = True

'Makes a copy of selected tabs and moves both to the same new workbook
Sheets(Array("Report Tab1", "Report Tab2", "Report Tab3")).Copy

Set wb = ActiveWorkbook

Worksheets("Report Tab3").Visible = False

'Breaks Links to the original workbook
BreakAllLinks wb

End Sub

Note: It's conceivable this becomes a moot point, as we have done some Knime flows that create these individual sheets and mails them out before for other programs, but I'm a little concerned about the scale here, and even if that's not a problem, I'm still curious if this can be made a more on-demand thing via vba.

Thanks!

 

[frobeniusabnorm]

Update with progress:

Found a promising solution but I'm having an issue still. Ultimate plan is to move the data table over with everything, filter to the value I need, select/copy, unfilter, clear data, then paste what's on my clipboard.

It's trying, but I get "Run-time error '1004': Application-defined or object-defined error"

Seems to not successfully be able to grab the value to filter? It tries to filter from the desired field but doesn't put any value into the filter. I tried switching around the type in the Dim for that but no dice.

Note: I'm adapting this from something I found that ran a loop so there may be some extraneous stuff in here, and I haven't got so far as to put in any of the copy/paste stuff yet. See below:

Sub copysheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim FilterRange As Range
Dim PA As String
Dim Rcount As Long
Dim FieldNum As Integer
Dim LastRow As Long


'Unhides Tabs so they can be copied over
Worksheets("Report Tab3").Visible = True
Worksheets("Source Data").Visible = True

'Makes a copy of selected tabs and moves both to the same new workbook
Sheets(Array("Report Tab1", "Report Tab2", "Source Data", "Report Tab3")).Copy

Set wb = ActiveWorkbook
Set ws = Sheets("Report Tab1")

Range("D8").Value = PA

Set ws = Sheets("Source Data")

Set FilterRange = ws.Range("A1")
FieldNum = 1

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

FilterRange.AutoFilter Field:=FieldNum, Criteria1:=PA

Sheets("Dealer Information").Range("A2").Select
'Range("A2").Select
On Error Resume Next
Set mylastCell = Cells(1, 1).SpecialCells(xlLastCell)
mylastCelladd = Cells(mylastCell.Row, mylastCell.Column).Address
myrange = "A3:" & mylastCelladd
Range(myrange).Select
Selection.Copy


'Hides tabs in new sheet
Worksheets("Report Tab3").Visible = False
Worksheets("Source Data").Visible = False

'Breaks Links to the original workbook
BreakAllLinks wb

End Sub

Any assistance would be appreciated!

 

[frobeniusabnorm]

Additional Update: Tried hardcoding a PA value, and the filter did better that time but I still got the same run-time error, which means I have two problems. Somehow it's not grabbing the PA value right, and the run-time error is probably coming from the select section.