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").Val ue
HomeBook = ActiveWorkbook. Name
Workbooks.Open Filename:="Sour ce.xls"
OtherBook = ActiveWorkbook. Name
Cells.Select
Selection.Copy
Windows(HomeBoo k).Activate
Sheets("BackUp" ).Select
Range("A1").Sel ect
ActiveSheet.Pas te
Application.Dis playAlerts = False
Workbooks(Other Book).Close SaveChanges:=Fa lse
Application.Dis playAlerts = 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]

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:



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.

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]

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

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