March 18, 2014

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.




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)

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

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

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

  1. 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!

  2. Dear Sir,
    can you help us, how do i paste data using paste special value for this code

  3. 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

  4. 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.


  5. 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.

    ‘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.

    1. I had one this year too, a Black copper Maran no less. I tried to nurse her along, but at 4 months she was tall, frail, and weighed nothing. What was amazing is she looked fine one day and then she didn’t when she was about 2 months old.

  6. 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..

  7. hi friends !!

    i am wondering if some one can help me to create a VBA to copy data from sheet1 to sheet 2

    COLUMN represents the number of different task (example task A,B C and so on)from column E6 TO J6
    ROW represents the date ( 13/02, 14/2 and so on mon to fri) from C7 to C11


    i would like to copy the number from sheet1 to sheet2 each day and at the end of the week click the button on sheet1 to clean the data in order to start new week.

    however the data , copied onto sheet 2 should not be deleted as we need that stats for reporting purpose.

    sheet2 will have capacity to save data of each day for different task from sheet1

    is this possible using a macro button


  8. Hi Dinesh,

    Thanks for your website.

    I have the following code. It is working well. Copying data from columns A:C to P:R when the records fall in a date range and have “No” in column D.

    How do I change the code to copy the data into another sheet?

    Sub CreateReport()
    Dim sDate As Date
    Dim tDate As Date
    Dim PayDay As Date
    Dim DueDate As Date
    Dim PayCal As String
    Dim LastRow As Integer
    Dim i As Integer


    sDate = Range(“G2”).Value
    tDate = Range(“G3”).Value
    PayDay = Range(“G4”).Value
    PayCal = Range(“G5”).Value

    ActiveCell.FormulaR1C1 = “Name”
    ActiveCell.FormulaR1C1 = “Due Date”
    ActiveCell.FormulaR1C1 = “Amount”
    Selection.Font.Bold = True
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With

    LastRow = ActiveSheet.Range(“A1”).CurrentRegion.Rows.Count

    For i = 2 To LastRow
    DueDate = Cells(i, 2).Value
    If (DueDate >= sDate And DueDate <= tDate) And (Cells(i, 4).Value = "No") Then
    Range(Cells(i, 1), Cells(i, 3)).Copy
    Range("P100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
    Application.CutCopyMode = False
    Cells(i, 4).Value = "Yes"
    End If
    Next i

    End Sub

    Thank you


  9. Dear Sir.

    It is great seeing your replies for so many random users. Apparently am new to VBA and rely on google. I have created 10 score cards which has detailed KPI. First sheet of every workbook is linked to some specific cells in different linked sheets (under same workbook). I only want to copy the first sheet of every workbook into a master file. Every first sheet will be copied to different sheets in master file. Please advice! Also the individual workbooks are VBA enabled so can this be implemented in such structure

Comments are closed.