How to copy each row from a worksheet into new workbooks?

Copy worksheet from multiple workbooks

• Hi Isaac I have multiple workbooks in a folder. each workbook is the same format I want to copy sheet2 from each woorkbook into a new workbook and have it rename the sheet that it just copied to the name of the workbook it just copied it from. once it is done it must close all the workbooks except for the new one, with the multiple sheets now. I was gonna use somethink like this Workbooks.Open Filename:="\Path ofworkbookFilename.xls" Sheets("Sheet2").Select Sheets("Sheet2").Copy Windows("Book2").Activate Sheets("Sheet2").Select Sheets("Sheet2").Name = "Filename" But that is very manual process how could I get it to do it with varibles by fetching each workbook and renaming it the name of that work book. Any help would be appricated.

• Answer:

  Credit .. I got most of this from mrexcel.som I believe, just tweaked it for you. Sub CombineSheets() Dim wbOpen As Workbook Dim wbNew As Workbook 'Change Path Const strPath As String = "U:\test\" Dim strExtension As String On Error Resume Next ChDir strPath 'Change extension if you want strExtension = Dir("*.xls") Set wbNew = Workbooks.Add 'Change Path, Name and File Format wbNew.SaveAs Filename:="U:\Test\Combined", FileFormat:=xlWorkbookNormal Do While strExtension <> "" Set wbOpen = Workbooks.Open(strPath & strExtension) With wbOpen .Sheets("Sheet2").Copy After:=wbNew.Sheets(wbNew.Sheets.Count) wbNew.Sheets(wbNew.Sheets.Count).Name = wbNew.Sheets(wbNew.Sheets.Count).Cells(1, 1) .Close SaveChanges:=False End With strExtension = Dir Loop On Error GoTo 0 End Sub