Copy Data From One Workbook to Another to a Specific Worksheet

Home More Excel (VBA) Training Videos








How to copy data from one workbook to another to a specific worksheet based on a viewer's question:

Hi Sir,

I have been trying to search on the internet with regards to trying to write a macro for a project of mine but I just can't seem to find it.

I sincerely seek your kind help in assisting me.

Basically, I have a workbook that keys in a daily report of a unique tag number. I would like to run a macro to pull data from that workbook to another workbook (master list) that will match the equipment tag number and record the problem so that I don't have to manually key it in. That way, the master list workbook will be able to track the historical trend of problems for the unique tag number, recording the date of when it happen and the problem that is being reported.

Is it possible to be done?

The file for the report is name 'SBR Oct to Dec 2013', tag number to be matched with master list in another workbook would be column B and data to be copied to master list after identifying the tag number would be column C and D.

The file for the master list is name 'SBR Historical Trend (Master)' in which the raw data has been seperated into different tabs. the column historical trend is meant to record the dates of which the problem occured and record the data for both column C & D of the report in the previous workbook.

Any ideas on how i should create the macros?

I have attached the file for your reference.

Thanks.

Regards,

Alfred


The complete code for the solution is given below:

Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

On Error Resume Next  ' We turn off error checking
FF = FreeFile()  ' The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF 'we try to open the file and lock it
Close FF ' Close the file
ErrNum = Error ' capture the error number
On Error GoTo 0  ' Turn on error checking
'Find which error happened
Select Case ErrNum
' No error
' File is not  open
Case 0: IsWorkBookOpen = False
 ' Error  for "Permission Denied."
 ' File already opened by another user
Case 70: IsWorkBookOpen = True
' Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Private Sub CommandButton1_Click()

Product$ = Right(Range("A10"), 2)
Range("A10:E10").Copy

Dim info
info = IsWorkBookOpen("C:\users\takyar\Desktop\SBR-Historical-Trend-Master.xlsx")
' we open the workbook if it is closed
If info = False Then
Workbooks.Open FileName:="C:\users\takyar\Desktop\SBR-Historical-Trend-Master.xlsx"
End If

Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' The following line shows how to reference a sheet within
            ' the loop using a simple text function
            If Right$(ActiveWorkbook.Worksheets(I).Name, 2) = Product$ Then
            Worksheets(I).Activate
           
           End If
'Find the first empty row in worksheet
 erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 ActiveSheet.Paste Destination:=Worksheets(I).Rows(erow)
 Next I

End Sub



Watch the video on YouTube

Reference
Home More Excel (VBA) Training Videos