Macro-Copy data from one workbook to another

[Closed]
Report
-
 Dvibe -
All, I need to copy data from one workbook and append the content to another workbook (WB).

Ex: WB1 (source) has

1 2 3 4 5

WB2 (target) already have

6 7 8 9 0

After running the macro,

WB2 should have

6 7 8 9 0
1 2 3 4 5

The formats of both the workbooks is same.

This will be of great help.

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this. Make sure that you read the NOTE in the code.


Sub CopyData()
Dim sBook_t As String
Dim sBook_s As String

Dim sSheet_t As String
Dim sSheet_s As String

Dim lMaxRows_t As Long
Dim lMaxRows_s As Long

Dim sMaxCol_s As String

Dim sRange_t As String
Dim sRange_s As String

    sBook_t = "Target Data WB- Copy data to WB.xls"
    sBook_s = "Source Data WB - Copy data to WB.xls"
    
    sSheet_t = "Target WB"
    sSheet_s = "Source"
    
    lMaxRows_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Row
    lMaxRows_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(Rows.Count, "A").End(xlUp).Row
    
    sMaxCol_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(1, Columns.Count).End(xlToLeft).Address
    sMaxCol_s = Mid(sMaxCol_s, 2, InStr(2, sMaxCol_s, "$") - 2)
    
    If (lMaxRows_t = 1) Then
        sRange_t = "A1:" & sMaxCol_s & lMaxRows_s
        sRange_s = "A1:" & sMaxCol_s & lMaxRows_s
        
        Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
            
    Else
        sRange_t = "A" & (lMaxRows_t + 1) & ":" & sMaxCol_s & (lMaxRows_t + lMaxRows_s - 1)
        sRange_s = "A2:" & sMaxCol_s & lMaxRows_s
        
        Workbooks(sBook_t).Sheets(sSheet_t).Range(sRange_t) = Workbooks(sBook_s).Sheets(sSheet_s).Range(sRange_s).Value
        
        ' ###################### NOTE #################
        'the following lines are to be used of serial number is to be fixed too, instead of being copied
        ' if there is no need, then delete the line below
        Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t).AutoFill Destination:=Workbooks(sBook_t).Sheets(sSheet_t).Range("A" & lMaxRows_t & ":A" & (lMaxRows_t + lMaxRows_s - 1)), Type:=xlFillSeries
    End If
            
End Sub
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Excellent, amazing. Its working fine when I opened both the files by same excel process.
But my requirement is that I wont be opening the target file. The target file stays on a server and takes much longer time to open. Thats why I am trying for a macro to avoid opening the target file. I will have a temporary file on my desktop which I wil be using as source.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
With excel that is not possible as far as I know, Both books needs to be open at the time of execution. The best you can do is to have the macro open the book, instead of you manually opening it
how can we open the work book by writing a macro? will it reduce time? or will it open at a predefined time?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Excel provided capability of recording macro. If you record your actions via macro, you can find a lot. Using the recorder, I got this code, You would open the book like this

workbooks.Open Filename:="C:\Users\shamikh\Documents\bookmarks.xls"

No It will not reduce time in opening. It just allows you to leave your workstation while macro opens the book and runs

You have to program to open at a schedule time. Basically you would some scheduler which you will use to open the book. Book by itself cannot open at a set time, Some thing else must open it and hence need of scheduler.
I tried the code and I getting a runtime error 9 on the following line Subscript out of range.
lMaxRows_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Row
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee. Have few rows of data
Source WB where I will have the data and macro. Once I update the data here and run the macro, it should get appended from the last row in Target WB. The content must be copied from the 2nd column onwards.
https://authentification.site/files/22081242/Source_Data_WB_-_Copy_data_to_WB.xls

Target WB
https://authentification.site/files/22081254/Target_Data_WB-_Copy_data_to_WB.xls

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!