Copy a particular workbook with data from another workbook

[hiitzsdg]

Hi All,

I am completely new to VBA and I am trying to develop a macro in a workbook (Backup.xls). The main functionality of the macro is:

1. It would open another workbook (Source.xls).
2. This workbook is regularly updated. The previously updated rows are replaced with new rows everyday.
3. Copy the newly updated row.
4. Paste the copied row after the previously inserted row.

The number of rows that can be present in the source.xls can vary from 0 to n.

Let us assume that in my Backup.xls, the number of rows that are present currently is say 16.

Thus my requirement is:
The macro would open source.xls, copy the 3 newly inserted rows (say) and then paste the same from 17th row. The first row is the column name which is same in both the excels.

I have made up the following piece of code:

Sub FetchData()
Dim SourceFile As String
Dim HomeBook As String
Dim OtherBook As String

Sheets("BackUp").Select
SourceFile = Range("A1").Value
HomeBook = ActiveWorkbook.Name
Workbooks.Open Filename:="Source.xls"
OtherBook = ActiveWorkbook.Name
Cells.Select
Selection.Copy
Windows(HomeBook).Activate
Sheets("BackUp").Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(OtherBook).Close SaveChanges:=False
Application.DisplayAlerts = True

End Sub

The problem that I am facing is after I am executing the above piece of code, the backup.xls is having the newly inserted rows only. The previously inserted rows are overwritten.

[MikeTheBike]

@hiitzsdg
Hi

Without knowing too much about what you are copying, perhaps this will give you a start ?

Sub FetchData()
    Dim SourceFile As String
    Dim HomeBook As String
    Dim OtherBook As String
    Dim NextRow As Long
    Dim LastRow As Long
 
    Sheets("BackUp").Select
    SourceFile = Range("A1").Value
    HomeBook = ActiveWorkbook.Name
    Workbooks.Open Filename:=SourceFile
    OtherBook = ActiveWorkbook.Name
 
    Range("A65536").Select
    Selection.End(xlUp).Select
    LastRow = ActiveCell.Row
 
    Rows("1:" & LastRow).Select
    Selection.Copy
 
    Windows(HomeBook).Activate
 
    Sheets("BackUp").Select
 
    Range("A65536").Select
    Selection.End(xlUp).Select
    NextRow = ActiveCell.Row + 1
    Range(Cells(NextRow, 1).Address).Select
 
    ActiveSheet.Paste
 
    Application.DisplayAlerts = False
    Workbooks(OtherBook).Close SaveChanges:=False
    Application.DisplayAlerts = True
 
End Sub
 

HTH


MTB

[hiitzsdg]

Hey Mike!!! Thanks a ton for your help... I got a headstart and I would now be able to complete the rest....

[hiitzsdg]

I have 1 more question in regards to the above query. Is it possible for us to change the serial number.

In the Source.xls, the serial number always starts from 1 and it starts from A3 cell.
However, in backup.xls, the serial number should increase by 1 after the last serial number thats updated there. In other words, suppose the last serial # in backup.xls be 16, then my intent is to copy the 3 rows present in source.xls and paste the same in backup.xls with serial # as 17, 18 and 19 instead of 1, 2 and 3.

The serial number is available in the first column in both the sheets.

[MikeTheBike]

@hiitzsdg
Hi again

Glad you were able to take it and change to suit your needs.

Regarding your last question, perhaps a mod in line withn this would do it?

Sub FetchData()
    Dim SourceFile As String
    Dim HomeBook As String
    Dim OtherBook As String
    Dim NextRow As Long
    Dim LastRow As Long
 
    Sheets("BackUp").Select
    SourceFile = Range("A1").Value
    HomeBook = ActiveWorkbook.Name
    Workbooks.Open Filename:=SourceFile
    OtherBook = ActiveWorkbook.Name
 
    Range("A65536").Select
    Selection.End(xlUp).Select
    LastRow = ActiveCell.Row
 
    Rows("1:" & LastRow).Select
    Selection.Copy
 
    Windows(HomeBook).Activate
 
    Sheets("BackUp").Select
 
    Range("A65536").Select
    Selection.End(xlUp).Select
    NextRow = ActiveCell.Row + 1
    Range(Cells(NextRow, 1).Address).Select
 
    ActiveSheet.Paste
 
    Application.DisplayAlerts = False
    Workbooks(OtherBook).Close SaveChanges:=False
    Application.DisplayAlerts = True
 
    Dim i As Long
    i = NextRow
 
    Do Until Cells(i, 1) = ""
        Cells(i, 1) = Cells(i - 1, 1) + 1
        i = i + 1
    Loop
 
End Sub

MTB