Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy two excel files into third with one header

  Asked By: Joao    Date: Mar 26    Category: MS Office    Views: 1607
  

Can any one please tell me how to i copy two excel file into third new file.
Both excel files have only one sheet and same header. Is it possible that the
new file contains only one header?

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Fedde Bakker     Answered On: Mar 26

I mean A1,B1,C1,D1....L1. They are basically the header  of each column
Thanks.

 
Answer #2    Answered By: Taylor White     Answered On: Mar 26

: I mean A1,B1,C1,D1....L1. They are basically the header  of each column

So, you want to take the data in each column of one worksheet in
each of two different workbooks and combine them into one new workbook
with a single worksheet. The heading for each column should be the same
as for one sheet  (since the headings are the same in each sheet).

Is that right?

How do you wish to combine the columns?

 
Answer #3    Answered By: Cay Nguyen     Answered On: Mar 26

Yes you are right. Combine means first file  data is first then at end of the
first file data second file data will be append but only one header  in the new
file (either from first file or the second one doesnt matter because they both
have same headers).

 
Answer #4    Answered By: Corbin Jones     Answered On: Mar 26

: Yes you are right.

Of course I am. I'm a guy!


: Combine means first file  data is first then at end of the first
: file data second file data will be append but only one header  in
: the new file (either from first file or the second one doesn't
: matter because they both have same headers).

Okay. Let's describe the data. Does the script have to locate
the data or is it in a named or fixed range?

If the script has to locate the data in each sheet, does the
range of cells ever contain empty cells?

Will it ever contain empty cells in the future?

How do we find the header in the second file? Is it always in
a certain row? At the top of the data? Is it in just one row or
many? Is it a named range?

 
Answer #5    Answered By: Taylor Evans     Answered On: Mar 26

: Combine means first file  data is first then at end of the first
: file data second file data will be append but only one header  in
: the new file (either from first file or the second one doesn't
: matter because they both have same headers).

Okay. Let's describe the data. Does the script have to locate
the data or is it in a named or fixed range?
Both files  have same column name and same values accept one has less data

If the script has to locate the data in each sheet, does the
range of cells ever contain empty cells?
No. Also there is only one sheet  in both file which contains data

Will it ever contain empty cells in the future?
No

How do we find the header in the second file? Is it always in
a certain row? At the top of the data? Is it in just one row or
many? Is it a named range?
At the top of the data, first row in both file and just one row

 
Answer #6    Answered By: Benjamin Simpson     Answered On: Mar 26

: If the script has to locate the data in each sheet, does the
: range of cells ever contain empty cells?
: No. Also there is only one sheet  in both file  which contains
: data

Are you implying that there may be extra blank worksheets
in one or both workbooks.


: Will it ever contain empty cells in the future?
: No

Fine. We can use the UsedRange property to define all the data
in each sheet.


: How do we find the header  in the second file? Is it always in
: a certain row? At the top of the data? Is it in just one row or
: many? Is it a named range?
: At the top of the data, first row in both file and just one row

If the sheet is the active sheet:

Dim rData As Range, _
rHeader As Range, _
rBody As Range

Set rData = ActiveSheet.UsedRange.Cells
Set rHeader = rData.Rows(1)
Set rBody = rData.Rows("2:" & rData.Rows.Count)



Great. We're almost there. What are the names of the files  used
to supply the data? Are they always the same names or do they change
each time? What is the name of the new workbook file?

 
Answer #7    Answered By: Adalwen Fischer     Answered On: Mar 26

What are the names of the files  used to supply the data?
names could be any but its good if they are in a variable

Are they always the same names or do they change
each time? What is the name of the new workbook file?
It could be any doesnt matter. But its good if its in a variable so that i can
change the name at any time.

 
Answer #8    Answered By: Dylan Evans     Answered On: Mar 26

I am pressed for time and cannot give you a complete, tested
solution. Here are some pieces you might need.

You can find the code needed to create a new workbook on line (with
a Google search) or by recording a macro to do that. It would look
something like this.

Sub CreateWorkbook(Filename As String)
Dim wbNew As New Workbook
Set wbNew = Workbooks.Add
wbNew.SaveAs Filename:=Filename
Set wbNew = Nothing
End Sub

Sub PasteData(Source1 As Worksheet, Source2 As Worksheet, wsDestination As
Worksheet)

Dim sNextOpenCell As String
sNextOpenCell = "A" & Source1.UsedRange.Rows.Count + 1

' Copy all the data from source 1
Source1.UsedRange.Copy Destination:=wsDestination.Range("A1")

' Copy only the data body from source 2
BodyRows(Source2.UsedRange.Cells).Copy
Destination:=wsDestination.Range(sNextOpenCell)

End Sub
Function BodyRows(rData As Range) As Range

' The body of data (column headings stripped)
Set BodyRows = rData.Range("2:" & rData.Rows.Count)

End Function

 
Didn't find what you were looking for? Find more on Copy two excel files into third with one header Or get search suggestion and latest updates.




Tagged: