Moving data between workbooks

[Trueblue862]

Hi,
I'm having trouble with this piece of code, I'm trying to search data in one workbook and move it into another workbook to a sheet that is named the same as TbxA.value. It's searching fine but when I try and output the data I can't figure out how to reference the second workbook so it outputs the data there. I have tried numerous ways of referencing the second workbook, but I just can't get it to work. Any help with this would be appreciated.

Private Const sht1 = "Call Log"
Private Const firstRow_sht1 = 2
Private Const findCol = 1
Private Const inputCol1 = 1
Private Const inputCol2 = 2
Private Const inputCol3 = 3
Private Const inputCol4 = 4
Private Const inputCol5 = 6
Private Const inputCol6 = 7
Private Const inputCol7 = 8




Private sht2 As Variant
Private Const firstRow_sht2 = 1
Private Const outputCol1 = 1
Private Const outputCol2 = 2
Private Const outputCol3 = 3
Private Const outputCol4 = 4
Private Const outputCol5 = 5
Private Const outputCol6 = 6
Private Const outputCol7 = 7




Function outputFunction(output1, output2, output3, output4, output5, output6, output7)


outputRow = Sheets(sht2).Cells(rows.Count, outputCol1).End(xlUp).Row + 1
Sheets(sht2).Cells(outputRow, outputCol1).Value = output1
Sheets(sht2).Cells(outputRow, outputCol2).Value = output2
Sheets(sht2).Cells(outputRow, outputCol3).Value = output3
Sheets(sht2).Cells(outputRow, outputCol4).Value = output4
Sheets(sht2).Cells(outputRow, outputCol5).Value = output5
Sheets(sht2).Cells(outputRow, outputCol6).Value = output6
Sheets(sht2).Cells(outputRow, outputCol7).Value = output7

End Function
Sub SearchData()

sht2 = UCase(Srch_Frm.TbxA.Value)
 textboxA = UCase(Srch_Frm.TbxA.Value) 'Enter your textbox value here.
R = firstRow_sht1
lastRow_sht1 = Sheets(sht1).Cells(rows.Count, findCol).End(xlUp).Row
Do Until R > lastRow_sht1
DoEvents
findValue = UCase(Sheets(sht1).Cells(R, findCol).Value)
If findValue = textboxA Then
output1 = Sheets(sht1).Cells(R, inputCol1).Value
output2 = Sheets(sht1).Cells(R, inputCol2).Value
output3 = Sheets(sht1).Cells(R, inputCol3).Value
output4 = Sheets(sht1).Cells(R, inputCol4).Value
output5 = Sheets(sht1).Cells(R, inputCol5).Value
output6 = Sheets(sht1).Cells(R, inputCol6).Value
output7 = Sheets(sht1).Cells(R, inputCol7).Value



Call outputFunction(output1, output2, output3, output4, output5, output6, output7)
End If
R = R + 1
Loop

 End Sub

 

[Zot]

You may refer here

VBA Copy Range to Another Sheet + Workbook

this tutorial shows you how to use VBA to copy a cell or a range to another sheet in the same workbook (or even in another workbook).

excelchamps.com

Copy data from one WorkBOOK to another WorkBOOK using VBA

Hi, I have some workbooks where information is held and i need to copy this information over to a master spreadsheet where everything is held. Would be really appreciated if you could support me in creating the VBA code for this, NOTE: i am a beginner so do not know too much, but i am...

www.mrexcel.com