help please! need a macro which can combine data from 2 excel sheets
 

I have an excel file with some data till row 50.(can be more or less)
and another excel file with data till row 100 (can be more or less)

what i want is that i get a macro which automatically adds the data
from second sheet below the data in sheet 1.

the data in first file can end on row 50 or 60 or at any row

thank u so much

any ideas


and i have to combine more 30 files like these into that first file.


---
Message posted from http://www.ExcelForum.com/

help please! need a macro which can combine data from 2 excel sheets
 

Hi

You Could use this


Code
-------------------

Sub CopySheets()
Dim Wks As Worksheet
Dim rng As Range
Dim NumberofSheets As Integer
Dim I As Integer

'Set variables
Set Wks = ActiveSheet
Set rng = Wks.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
NumberofSheets = Worksheets.Count

'copy the data accross and change the initial paste cell
For I = 2 To NumberofSheets
Worksheets(I).UsedRange.Copy Destination:=rng
Set rng = Wks.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
Next I

End Sub

-------------------


with the sheet you are copying to as the first sheet of the curren
workbook.

HTH

Davi

--
Message posted from http://www.ExcelForum.com

help please! need a macro which can combine data from 2 excel sheets
 

Hi vikram

Try this example
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"vikram " wrote in message ...
I have an excel file with some data till row 50.(can be more or less)
and another excel file with data till row 100 (can be more or less)

what i want is that i get a macro which automatically adds the data
from second sheet below the data in sheet 1.

the data in first file can end on row 50 or 60 or at any row

thank u so much

any ideas


and i have to combine more 30 files like these into that first file.


---
Message posted from http://www.ExcelForum.com/

help please! need a macro which can combine data from 2 excel sheets
 

As long as there are no spaces you can use .end eg

Range("A2").End(xlDown).Offset(1, 0).Select

will give you the equivalent of selecting A2 then pressing Ctrl+down arrow,
then pressing down again - taking you to the next available cell. You can
then copy and paste from your files into the sheet. Some kind of loop would
help, going through each file, opening, copying, pasting in your workbook,
closing then moving down as above before repeating.

Sorry it's a bit vague, but I think you get the idea :)

Jeff

"vikram " wrote in message
...
I have an excel file with some data till row 50.(can be more or less)
and another excel file with data till row 100 (can be more or less)

what i want is that i get a macro which automatically adds the data
from second sheet below the data in sheet 1.

the data in first file can end on row 50 or 60 or at any row

thank u so much

any ideas


and i have to combine more 30 files like these into that first file.


---
Message posted from http://www.ExcelForum.com/

help please! need a macro which can combine data from 2 excel sheets
 

thank u.....worked amazingl

--
Message posted from http://www.ExcelForum.com

help please! need a macro which can combine data from 2 excel sheets
 

hello Jeff! I need a macro which can combine data from 2 differen
workbooks and not 2 different sheets from 1 workbook!

thank

--
Message posted from http://www.ExcelForum.com

help please! need a macro which can combine data from 2 excel sheets
 

Try a bit of code that does something like this:

ThisWorkbook.Sheets("Masterlist").Range("a2").Sele ct
For intCycle = 1 To 30 ' number of files here
ThisWorkbook.Sheets("Masterlist").Range("a2").End( xlDown).Offset(1,
0).Select
strFilename = Sheets("filelist").Range("a" & intCycle).Value
Workbooks.Open ("C:\Temp" & strFilename & ".xls")
Sheets("list").Range("a1").Select ' you can change this based on the
sheet in the individual files
Range(Selection, Selection.End(xlDown)).Copy
ThisWorkbook.Sheets("Masterlist").Select
ActiveSheet.Paste
Workbooks(strFilename).Close (False)
ThisWorkbook.Sheets("Masterlist").Range("a2").End( xlDown).Offset(1,
0).Select
Next intCycle

You need a sheet with the list of files on it from A1 downwards called
filelist, and a sheet called Master list where all the data will be copied
into. I think that will work.

Jeff

"vikram " wrote in message
...
hello Jeff! I need a macro which can combine data from 2 different
workbooks and not 2 different sheets from 1 workbook!

thanks


---
Message posted from http://www.ExcelForum.com/

help please! need a macro which can combine data from 2 excel sheets
 

Try this then
http://www.rondebruin.nl/copy3.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"vikram " wrote in message ...
hello Jeff! I need a macro which can combine data from 2 different
workbooks and not 2 different sheets from 1 workbook!

thanks


---
Message posted from http://www.ExcelForum.com/