PDA

View Full Version : [SOLVED] Using VBA how to Merge one excel file worksheet data to another file worksheet?



bunty
02-27-2012, 11:50 AM
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
02-27-2012, 06:00 PM
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
02-29-2012, 12:21 AM
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
02-29-2012, 09:13 AM
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
02-29-2012, 01:03 PM
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
02-29-2012, 02:44 PM
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
03-01-2012, 06:30 AM
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
03-01-2012, 10:48 AM
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
03-01-2012, 12:52 PM
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:type 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
03-01-2012, 01:11 PM
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
03-02-2012, 05:26 AM
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
03-02-2012, 09:08 AM
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
03-02-2012, 12:22 PM
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.