Hello relatively new to using
VBA
& Macro's , I am trying to setup a macro that will prompt the user via input box to type a client name in , it will then search the closed workbook(Quick Test.xlsm)"Sheet 2" (Companies) for the client name and once found pull the entire row of data through to the open workbook(Test2.xlsm)"Sheet 1"(Master) and paste in the next available row(A5).
Below is the code I have started to create however I am not sure how to reference each workbook and which one to search in and where to paste to. At present when I click the button the code is attached to it just copies data from the current sheet:
Thanks in advance i am sure the code is very simple, i have searched on google and found similar posts regarding linking multiple workbooks but still cant seem to work it out.
Below is the code I have started to create however I am not sure how to reference each workbook and which one to search in and where to paste to. At present when I click the button the code is attached to it just copies data from the current sheet:
Code:
Dim srcWorkbook As Workbook
Dim destWorkbook As Workbook
Dim srcWorksheet As Worksheet
Dim destWorksheet As Worksheet
Dim SearchRange As Range
Dim destPath As String
Dim destname As String
Dim destsheet As String
Set srcWorkbook = ActiveWorkbook
Set srcWorksheet = ActiveSheet
Dim vnt_Input As String
vnt_Input = Application.InputBox("Please Enter Client Name", "Client Name")
destPath = "C:\test\"
destname = "Test2.xlsm"
destsheet = "Sheet1"
Set destWorkbook = Workbooks(destname)
If Err.Number <> 0 Then
Err.Clear
Set wbTarget = Workbooks.Open(destPath & destname)
CloseIt = True
End If
For Each c In Range("A2:W100")
If InStr(c, vnt_Input) > 0 Then
c.EntireRow.Copy
destWorkbook.Activate
destWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
srcWorkbook.Activate
End If
NextThanks in advance i am sure the code is very simple, i have searched on google and found similar posts regarding linking multiple workbooks but still cant seem to work it out.
Last edited by a moderator:
