Copy Data From One Workbook to Another to a Specific Worksheet

How to Copy Data From One Workbook to Another to a Specific Worksheet Using VBA
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:


Further reading:
Macro to Loop Through All Worksheets in a Workbook

6 thoughts on “Copy Data From One Workbook to Another to a Specific Worksheet

  1. Shio Ibarra

    Excellent work, I modified this code to fit my project and it worked fantastically, you are very good explaining and keeping a logical path to do it. Thank you so, so much, so much. Just fantastic!

    Reply
  2. nirmala

    Dear Sir,
    I am developing a excel based climate model. I want to compare green house gas emissions from a project (lets day Scenario 1) as compared to BAU (Business as usual practice). I want users to enter monthly green house emissions data in scenario 1 (Range of data) . BAU data is constant. Once the user enter new data to scenario 1 and click “save data” button, it should be saved in the table below under the corresponding month. Then the data entering cells should be cleared for them to enter data again next month . Could you please help me on this. Many thanks
    Sincerely,
    Nirmala

    Reply
  3. Jagadeesh J

    Hi Sir,

    I am Jagadeesh from India, first of all I would like to say many thanks for helping us on solving our problems, you are doing a very great job.

    I need a solution on copying two sheets of data into another sheet(it might be user specific).

    for example, I am having more than 10 sheets, however I don’t want to combine all of them, I want only sheet A and Sheet B to be copied and pasted to the sheet C, the sheet A and B may have repeated data so while copying it to sheet C those data should be deleted.

    Thanks,
    Jagadeesh

    Reply
  4. Abil

    Hi,

    I need to transfer/copy data from one workbook (Source) to another workbook (Workbook A). The problem is, I will need to transfer specific data based on the requirement entered in WorkBook A.

    Example:
    ‘WorkBook A
    Month: January

    I need data from Sheet January (from Source) to fill into my WorkBookA. And if i enter month to be February, i need the data from Sheet February (Source) to be transferred and so on.

    I wonder should i use Loop for this? If i do, how?

    Appreciate if you can help.

    Reply
  5. Markko

    Dear Sir,

    Thanks for all your great videos etc.. I have look these but it seem that it is too hard me to make that..
    So I ask then Help…

    I have many PO’s that I now collect one master sheet and I do that manually.
    I have never ever do any macro etc.. and I really appreciate if you could help me out on this.

    If you could help me where I can send sample files to look, I have make clear info what I need and there is 4 PO done. only MSTR is done manually..

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *