Using VBA how to Merge one excel file worksheet data to another file worksheet?

[bunty]

Hello,

I have two excel file that is abc.xls and xyz.xls.
Both file contain 3 worksheet with the same name (sheet1, sheet2 and sheet3) and heading.
I am trying to copy the data of sheet1 of abc.xls to sheet1 of xyz.xls
Sheet1 of XYZ.xls also has some data. So, abc.xls data will copy after that.
How to do it through macro.

Thanks....

[jolivanes]

If Workbook xyz.xls is not open
See if this works. (From romperstomper)

Sub OpenAndManipulate()
Dim DATABASE_WORKBOOK As Workbook
Dim DATABASE_SHEET As Worksheet
Dim DATABASE_RECORDS as range
Dim Template_Sheet As Worksheet
With Application
  .ScreenUpdating = False
  .EnableEvents = False
End With
Set DATABASE_WORKBOOK = Workbooks.Open("C:\Users\Owner\Documents\xyz.xls") '<----- Change as required
Set Template_Sheet = ThisWorkbook.Sheets("Sheet1")
Set DATABASE_SHEET = DATABASE_WORKBOOK.Worksheets("Sheet1")
COUNT_ROW = 1
Set DATABASE_RECORDS = DATABASE_SHEET.Columns(1).Rows(65536).End(xlUp)
Template_Sheet.UsedRange.Copy
DATABASE_RECORDS.Offset(0, 1).PasteSpecial xlPasteValues
Template_Workbook.Close SaveChanges:=False
With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With
    Set xlw = Nothing
End Sub

[bunty]

Hi Jolivanes,
Thanks for your quick response...But it’s not working as per the requirement. Little issue is there which I tried to find out but no luck.
1. The data is over writing the last row of (from 2nd column) of other sheet.
2. The heading is also copying, which is not required.
3. It’s throwing error when I am running a macro “error 424- object required”.
Could you please explain how the code is working?
Thanks...

[jolivanes]

What is the range you want to copy?
In the code it uses UsedRange which includes the Header Row.

Try this on a copy of your workbooks

Sub OpenAndManipulate()
Dim DATABASE_WORKBOOK As Workbook
Dim DATABASE_SHEET As Worksheet
Dim DATABASE_RECORDS As Range
Dim Template_Sheet As Worksheet
Dim COUNT_ROW As Single
Dim Template_Workbook As Workbook
With Application
  .ScreenUpdating = False
  .EnableEvents = False
End With
Set DATABASE_WORKBOOK = Workbooks.Open("C:\TempA\Book3.xls") '<----- Change as required
Set Template_Sheet = ThisWorkbook.Sheets("Sheet1")
Set DATABASE_SHEET = DATABASE_WORKBOOK.Worksheets("Sheet1")

COUNT_ROW = 1
Set DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, "A").End(xlUp)
Template_Sheet.UsedRange.Copy '<----- Change "UsedRange" to actual Range you want copied  
DATABASE_RECORDS.Offset(1, 0).PasteSpecial xlPasteValues
DATABASE_WORKBOOK.Close SaveChanges:=True
With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With
Application.CutCopyMode = False
End Sub

[bunty]

Hi Jolivanes,
Thank you so much.. its working fine...
I have one query.Is it possible to copy the particular column data from one file to other.
If any thing is there it will override that data.
example: column a of sheet1 (abc.xls) to column c of sheet1(xyz.xls)---if any data is available in column c except heading,it will override that.

once again thanks.. for your help..

[jolivanes]

Try This with copies of your workbooks.
It copies from A2 to the end of column A to column C (starting at Row 2) in Closed Workbook

Sub OpenAndManipulateCol()
    Dim DATABASE_WORKBOOK As Workbook
    Dim DATABASE_SHEET As Worksheet
    Dim DATABASE_RECORDS As Range
    Dim Template_Sheet As Worksheet
    Dim COUNT_ROW As Single
    Dim Template_Workbook As Workbook
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set DATABASE_WORKBOOK = Workbooks.Open("C:\TempA\Book3.xls")    '<----- Change as required
    Set Template_Sheet = ThisWorkbook.Sheets("Sheet1")
    Set DATABASE_SHEET = DATABASE_WORKBOOK.Worksheets("Sheet1")
    If DATABASE_SHEET.Range("C2") <> "" Then _
       DATABASE_SHEET.Range("C2", Range("C" & Rows.Count).End(xlUp)).ClearContents
    Set DATABASE_RECORDS = DATABASE_SHEET.Range("C2")
    Template_Sheet.Range("A2", Template_Sheet.Range("A" & Rows.Count).End(xlUp)).Copy
    DATABASE_RECORDS.PasteSpecial xlPasteValues
    DATABASE_WORKBOOK.Close SaveChanges:=True
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Application.CutCopyMode = False
End Sub

[bunty]

Hi Jolivanes,
Need some more guides from you. For moving one column and overriding column in other file its working fine. But when I am trying to move few more columns to other sheet it’s throwing error.
Here multiple columns are there which I am trying to move from one worksheet to other.
Example: column A of abx.xls to column A of xyz.xls
Column B of abx.xls to column D of xyz.xls
Column D of abx.xls to column I and J of xyz.xls

As per my understanding I tried to add this line again and changed the column name.
If DATABASE_SHEET.Range("A2") <> "" Then _
DATABASE_SHEET.Range("A2", Range("A" & Rows.Count).End(xlUp)).ClearContents
Set DATABASE_RECORDS = DATABASE_SHEET.Range("A2")
Template_Sheet.Range("A2", Template_Sheet.Range("A" & Rows.Count).End(xlUp)).Copy
Then it’s giving error called “run time error 1004 –method range of object ‘_worksheet’ failed.
Could you please let me know what the issue here is?
Thanks for all you help....

[jolivanes]

bunty.
You keep moving the goalposts. I can't see your workbook but it sounds like it could be easier if you copied the whole range, in other words all columns, over. Is that right?

[bunty]

Hi Jolivanes,

Yes I agree with you and sorry for inconvenience. Actually requirement it self changed.In target location (xyz.xls) the columns are not in a same order as it is in source sheet (asd.xls).Due to that we can not copy whole range of data at a time.
There is one twist also...few of the column are only available in target sheet.example column which has only one value for all the cell in that column.which I am increasing or decreasing as per the number of rows copied from source sheet.
Please find the attachment.

Thanks a lot...

[jolivanes]

I had this before I read your last post.
Is this any help?

Sub OpenAndManipulateColB()
    Dim DATABASE_WORKBOOK As Workbook
    Dim DATABASE_SHEET As Worksheet
    Dim DATABASE_RECORDS As Range
    Dim Template_Sheet As Worksheet
    Dim COUNT_ROW As Single
    Dim Template_Workbook As Workbook
    Dim MyValue1, MyValue2
    MyValue1 = InputBox(Prompt:="Enter the Column to copy", Title:="Select Column")
    MyValue2 = InputBox(Prompt:="Enter the Column to copy to", Title:="Select Column")
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set DATABASE_WORKBOOK = Workbooks.Open("C:\TempA\Book3.xls")    '<----- Change as required
    Set Template_Sheet = ThisWorkbook.Sheets("Sheet1")
    Set DATABASE_SHEET = DATABASE_WORKBOOK.Worksheets("Sheet1")
    If DATABASE_SHEET.Range(MyValue2 & 2) <> "" Then _
       DATABASE_SHEET.Range(MyValue2 & 2, Range(MyValue2 & Rows.Count).End(xlUp)).ClearContents
    Set DATABASE_RECORDS = DATABASE_SHEET.Range(MyValue2 & 2)
    Template_Sheet.Range(MyValue1 & 2, Template_Sheet.Range(MyValue1 & Rows.Count).End(xlUp)).Copy
    DATABASE_RECORDS.PasteSpecial xlPasteValues
    DATABASE_WORKBOOK.Close SaveChanges:=True
    MyValue1 = ""
    MyValue2 = ""
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Application.CutCopyMode = False
End Sub

You select the column to copy and also select the column to copy to in the closed workbook.

Let us know.

Regards
John

[bunty]

Hi John,

Its working fine but I have multiple columns (around 25) in source and target. If we use this ways it will take lot of time which is same as manual copy paste.
Is there any way to do that at a time? I mean to say in a macro we can map the source and target column.
Thanks...

[jolivanes]

I would not know how to do that efficiently other then having both sheets the same so you can move everything over in one block.
The other way would be to have two helper columns with "From Column" and "To Column" as headers and loop through these columns.
Maybe someone a lot smarter then me will come around and supply you with the code.

Regards
John

[bunty]

That's fine John... you helped me a lot. As I don't have much experiences on VBA coding but learned so many thing from you.
Once again I would like to thank you for your support.