Macro-Copy data from one workbook to another

Question

Macro-Copy data from one workbook to another

[Closed]

Report

Krishna - Apr 22, 2010 at 12:21 AM
Dvibe - Aug 27, 2015 at 12:13 PM

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.

Macro-Copy data from one workbook to another
Excel macro copy data from another workbook - How-To - Excel
Macro copy data from one workbook to another - How-To - Excel
Macro to copy data from one workbook to another based on criteria ✓ - Forum - Excel
Excel vba copy data from another workbook without opening ✓ - Forum - Excel
Excel - Run macro when data entered in a cell - How-To - Excel

3 replies

Answer 2 / 3

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
Apr 22, 2010 at 12:50 PM

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

Answer 3 / 3

Krishna
Apr 23, 2010 at 12:34 AM

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

Ask a question

rizvisa1 · Accepted Answer · 2010-04-23T11:22:45-0400

Answer 1 / 3

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
Apr 23, 2010 at 11:22 AM

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

View all 9 comments

Report

krishna
Apr 29, 2010 at 11:58 PM

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.

Report

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
Apr 30, 2010 at 06:40 AM

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

Report

Krishna
May 3, 2010 at 02:45 AM

how can we open the work book by writing a macro? will it reduce time? or will it open at a predefined time?

Report

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

768
May 3, 2010 at 07:12 AM

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.

Report

Dvibe
Aug 27, 2015 at 12:13 PM

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

Macro-Copy data from one workbook to another

3 replies

Your reply

Subscribe To Our Newsletter!