Copy data from one excel file to another excel file

priyaahh

Hi friends, Kindly help me for this. I have one excel sheet which has only 1 row (first row) which is heading. I have another excel sheet which has header row and data rows. I wanted to copy from the second excel sheet to first excel sheet based on the header using vba. Please help me... Thanks in Advance!! Regards,

Syed Wayez Ahmed

Even tou ur problem was not clear i tried to help you with what i have understood. You want to copy a sheet from a sheet, if a sheet has three columns below is the code.

Dim i As Integer
i = 1

While Trim(sheet2.Cells(i, 1)) <> ""
sheet1.cells(i,1)=sheet2.cells(i,1)
sheet1.cells(i,2)=sheet2.cells(i,2)
sheet1.cells(i,3)=sheet2.cells(i,3)
i=i+1
wend

If you want to keep the header as it is in sheet1 and want to copy the whole thing next to it, below is the code:

Dim i As Integer
i = 1

While Trim(sheet2.Cells(i, 1)) <> ""
sheet1.cells(i+1,1)=sheet2.cells(i,1)
sheet1.cells(i+1,2)=sheet2.cells(i,2)
sheet1.cells(i+1,3)=sheet2.cells(i,3)
i=i+1
wend

cheers 'S wayez 'http://wayezquotes.blogspot.com

Syed Wayez Ahmed

if you dont want to disturb both the header then

i=2

if you want to compare headers of both

if sheet1.cells(1,1)=sheet2.cells(1,1) then

Dim i As Integer
i = 2
While Trim(sheet2.Cells(i, 1)) <> ""
sheet1.cells(i,1)=sheet2.cells(i,1)
sheet1.cells(i,2)=sheet2.cells(i,2)
sheet1.cells(i,3)=sheet2.cells(i,3)
i=i+1
wend
End if

priyaahh

Hi, Thank you so much for replying me.. but i wanted to copy data from one excel file to another excel file... example in Excel file1, Sheet1 I have data for Emp Id, Emp Name etc., I have Excel file2, Sheet1 with only the headers in the first row like Emp Id, Emp Name ...etc..Which is exists some path in the hard drive. When I open Excel file1 and clicking some button, the data in the Excel file1 sheet1 should go to the Excel file2 sheet1 under approximate columns... Hope its understandable.... Kindly Reply.... Thanks lot again.. Regards,

Sonhospa

Hi, it's quite simple as long as the columns of both workbooks are consistent, i.e. if the first column of Workbook1.WorkSheet1 has to go to the first column of Workbook2.Worksheet1, etc. The following snippet works and can give you a first idea:

Sub CopyToTarget()
Dim tgt As Excel.Workbook
Dim target As String
Dim rng As Range

' define the range to copy here
' with End(xlDown) you catch all the rows as long as there are no empty cells.
Set rng = ActiveSheet.Range(Cells(2, 1), Cells(2, 1).End(xlDown))

' open the target workbook as object; in this case the workbook has to exist already!
Set tgt = Application.Workbooks.Open("D:\\Target.xls")

' copy the range to the required cell
rng.Copy tgt.Worksheets("Tabelle1").Cells(2, 1)

' save and close the target workbook
tgt.Save
tgt.Close

' delete objects that are no longer needed
Set rng = Nothing
Set tgt = Nothing

End Sub

If you have to identify the right column first, use the ranges method 'find' to determine the cell where you want to copy the data. In VBA, you will want to use IntelliSense as much as you can - it really helps you to find your way! :thumbsup:

priyaahh

Hi Michael, Thanks a lot!! Its working....but yes as u said..the sequence of the columns will not be simillar as per the source work book...some time the target workbook has more compare to source workbook...now i want to compare the column names and if matching then copy its range under that column... Thanks again..really its great help... Regards, Priya

Syed Wayez Ahmed

Good Priya jii! As michael said but if you want to check with headers Just check the range in target sheet and compare to data sheet if exists paste.

if ActiveSheet.Range(Cells(1, 1)="Emp Num" then
Set rng = ActiveSheet.Range(Cells(2, 1), Cells(2, 1).End(xlDown))
Set tgt = Application.Workbooks.Open("D:\Target.xls")
rng.Copy tgt.Worksheets("Tabelle1").Cells ' tgt.Save
tgt.Close
end if

Sonhospa

If you want to keep it even more flexible, you rather use 'find' - in case your EmpNum stands in a different column. Here's the modified idea, also checking for non existing header as Ahmed assumed. You can extend it in many different ways.

Sub CopyToTarget()
Dim tgt As Excel.Workbook
Dim rng As Range
Dim srcHeader As Range
Dim tgtHeader As Range
Dim dataCol As Integer

' define the range to copy here, supplying the column no you wish to copy
' with End(xlDown) you catch all the rows as long as there are no empty cells.
dataCol = 1
Set srcHeader = ActiveSheet.Cells(1, dataCol)
Set rng = srcHeader.Range(Cells(2, dataCol), Cells(2, dataCol).End(xlDown))

' open the target workbook as object; in this case the workbook has to exist already!
Set tgt = Application.Workbooks.Open("D:\\Target.xls")

' find the header of the given dataCol in row 1 of the target worksheet
Set tgtHeader = tgt.Worksheets("Table1").Rows(1).EntireRow.Find(srcHeader.Value)

' copy the range to the required cell
' if not found then notice the user
If tgtHeader Is Nothing Then
    MsgBox "Header not found in target workbook."
Else
    rng.Copy tgt.Worksheets("Table1").Cells(2, tgtHeader.Column)
End If

' save and close the target workbook
tgt.Save
tgt.Close

' delete objects that are no longer needed
Set rng = Nothing
Set srcHeader = Nothing
Set tgtHeader = Nothing
Set tgt = Nothing

End Sub

Maybe you google for samples of the Range object, they might even inspire you ;).

Sonhospa

Hi Priya, regarding your question from direct mail, how you should re-arrange the colunms, I want to give you a first idea again - not knowing if it's the best way ever, but it seems simple and pragmatic:

Sub ReArrange()
Dim strHeaders() As String
Dim sortOrder As String
Dim i As Integer

' define how the columns / headers are to be arranged
sortOrder = "EmpID,EmpName,EmpSalary"
' read the string into an array
strHeaders = Split(sortOrder, ",")

' loop through the array
For i = LBound(strHeaders) To UBound(strHeaders)
    ' take care: i will be 0 first (array index starting with 0)
    ' so you can't assign i to a colunm --> i + 1 is fine
    MsgBox "Header of column " & i + 1 & " = " & strHeaders(i)
    ' here you find the respective header and copy your headers and/or data.
    ' I guess you can adapt the code sample from before already, can you?
Next

End Sub

Hope that helps you - and don't forget to vote ;-)! :thumbsup:

priyaahh

Hi Michael, Thanks. Yes the one sheet which i have is so complicated format of row and column and data too...its not structured...So I need more effort for this to get done..Let me try and post if struckup inbetween... Still I manage to do, but the code I writing is becoming so lengthy ... Thanks again. Regards, Priya.

Sonhospa

If your source data are not structured then what I suggested doesn't make a lot of sense. It might be worth re-organizing your sheet (if possible) OR using range names. You can then refer to named range objects, e.g. Range("myCell") and do what ever with them. If it's so complicated and unstructured, did you already think about dropping the VBA idea and just referencing the respective cells from the (structured) target worksheet? If you don't get forward or too irritated ;) then feel free to send the workbook to my private address. Regards, Michael