I am creating a macro for a file that will be distributed to a team of people; the function is supposed to be able to pull the person's name from a different cell (in Variable B), search for that value in another workbook with multiple sheets (Variable X), and if found copy a specific range of cells from Workbook X to Workbook B.
I am having trouble with the following code:
Sub Pull_data_Click()
Dim A As Variant 'defines name from first subroutine
Dim B As Workbook 'defines destination file
Dim X As Workbook 'defines existing report file as source
Dim Destination As Range 'defines destination range of data pulled from report
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
A = Workbooks("filenameB.xlsm").Worksheets("Summary").Range("A1").Value 'constant variable, does not change
Set B = Workbooks("filenameB.xlsm") 'constant variable, does not change
Set X = Workbooks.Open("filenameX.xlsm") 'dependent variable, new name for each new report
Set Destination = Workbooks("filenameB.xlsm").Worksheets("Input").Range("B2:S2") 'Range changes for each iteration, rows increase by 1
'check if name is entered
If A = "" Then
MsgBox ("Your name is not visible; please start from the Reference tab.")
B.Worksheets("Reference").Activate
Exit Sub
End If
With X.Worksheets
For Each ws In X.Worksheets
Set rng = Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
ActiveCell.Activate
ActiveSheet.Range("$A$2:$DQ$11").AutoFilter Field:=1, Criteria1:=A
Range("A7:CD7").Select
Selection.Copy
B.Activate
Destination.Activate
Destination.PasteSpecial Paste:=xlPasteValues
Next ws
End With
Application.ScreenUpdating = False
End Sub
It is able to compile successfully and has no run-time errors, and when it runs it seems to be looping through the worksheets correctly...but it is pasting the wrong information. Is there anything in this I haven't set up properly?