Merging Files

[rglasunow]

Someone helped me with the code to merge all the files under one folder
onto one spreadsheet. However, from the code below is there a way I
can tell Excel to open a file and copy from a specific worksheet?

Application.ScreenUpdating = False
Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "" ChDrive FOLDERNAME
ChDir FOLDERNAME

Set Dest = Range("A2")
FName = Dir("*.xls")

Do Until FName = ""
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop
End Sub

Thank you!!

 

[Ron de Bruin]

WB.Worksheets(1).Rows(2).Copy Destination:=Dest

This will copy from the first worksheet( It use the sheet index)

If the worksheet names in the files have the same name you can use this
WB.Worksheets("yoursheet").Rows(2).Copy Destination:=Dest

I have also a example on this page
http://www.rondebruin.nl/copy3.htm

 

[rglasunow]

Thanks for your response. I realized one more problem. The sheet that
I want to copy is hidden. I tried to add some code to no avail.

Sub MergeFiles()
Application.ScreenUpdating = False
Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Excel Data"
ChDrive FOLDERNAME
ChDir FOLDERNAME

Set Dest = Range("A2")
FName = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
Exit Sub
End If

Do Until FName = "" '<<< Change do until file!!
Set WB = Workbooks.Open(FName)
Windows(FName).Activate
Sheets("Data").Visible = True
WB.Worksheets("Data").Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop
End Sub

Also I think that I need to do a copy/paste special when bringing the
data over. I couldn't figure out where I could put the code on this as
it looks like to me that this says to copy the data from the sheet to
the destination on the other sheet.

Any sugestions?

Thank you!

 

[rglasunow]

Please ignore my previous posting. I have found what the problem is.
However, I'm not sure how to fix it. I want to copy the 2nd row in al
the files and paste them in an on going spreadsheet on the master file
What is happening is it's pulling the next row down instead. S
instead of having cell B5 for all of column A it's going B5, B6, B7
etc...
Thanks,
RYa