I attached macro to the button to copy data from one report to another. I am getting an error: Compile error: User - defined type not defined. [enter image description here] enter image description here
I have the following code:
Private Sub CommandButton1_Click()
Dim fs As FileSystemObject
Dim srcBK As Workbook
Dim trgBK As Workbook
Set fs = New FileSystemObject
Dim i As Long
i = 2
While ThisWorkbook.Worksheets("MACRO").Range("A" & i).Value <> ""
If Not (fs.FileExists(ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("MACRO").Range("A" & i).Value)) Then
MsgBox "Source File " & ThisWorkbook.Worksheets("MACRO").Range("A" & i).Value & " does not exist"
Exit Sub
ElseIf Not (fs.FileExists(ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("MACRO").Range("D" & i).Value)) Then
MsgBox "Target File " & ThisWorkbook.Worksheets("MACRO").Range("D" & i).Value & " does not exist"
Exit Sub
Else
Set srcBK = Workbooks.Open(ThisWorkbook.Path & "\" & ThisWorkbook.Worksheets("MACRO").Range("A" & i).Value)
Set trgBK = ThisWorkbook
srcBK.Worksheets(ThisWorkbook.Worksheets("MACRO").Range("B" & i).Value).Select
srcBK.Worksheets(ThisWorkbook.Worksheets("MACRO").Range("B" & i).Value).Range(ThisWorkbook.Worksheets("MACRO").Range("C" & i).Value).Select
Selection.Copy
trgBK.Activate
trgBK.Worksheets(ThisWorkbook.Worksheets("MACRO").Range("E" & i).Value).Select
trgBK.Worksheets(ThisWorkbook.Worksheets("MACRO").Range("E" & i).Value).Range(ThisWorkbook.Worksheets("MACRO").Range("F" & i).Value).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
trgBK.Save
srcBK.Close savechanges:=False
End If
i = i + 1
Wend
MsgBox "Copying Done"
End Sub