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

[vikram]

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.

 

[DSC]

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

 

[Ron de Bruin]

Hi vikram

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

 

[Jeff Standen]

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]

thank u.....worked amazingl

 

[vikram]

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

thank

 

[Jeff Standen]

Try a bit of code that does something like this:

ThisWorkbook.Sheets("Masterlist").Range("a2").Select
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

 

[Ron de Bruin]

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