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

Vba copy multiple workbooks to one sheet

• QUESTION: There are multiple workbooks with one sheet each in a directory. All sheets within each workbook have the same column headings in row A. I would like tweak the code below offered my you to copy and paste the values from each workbook to one workbook on one sheet starting with the last row. For example, Workbook 1, sheet 1 would have data copied to a master workbook using the lastrow of data Workbook 1 Sheet 1 Copies and Paste from Row 2...50 Row 1 2 .. 50 Workbook 2 Sheet 1 Copies and Paste from Row 51 ...75 Row 51 52 ... 75 Here is the code that I was trying to tweak. It works, and copies and paste, but it doesn't paste starting with the last row. It writes over the current rows. Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range Dim r1 As Range, sh As Worksheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) Set sh = BaseWks 'Set sh = ActiveSheet ' this is the summary sheet sPath = "D:\FAA 02 copied SPREADSHEETS\" sName = Dir(sPath & "*.xls?") Do While sName <> "" Set bk = Workbooks.Open(sPath & sName) Set r = bk.Worksheets(1).Range("A1").CurrentRegion Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(1) r.Copy r1.PasteSpecial xlValues r1.PasteSpecial xlFormats bk.Close SaveChanges:=False sName = Dir() Loop ANSWER: Mike, Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(1) should be Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(2) or Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1,0) they are equivalent. this assumes that the copied data will have values in column A in the last row pasted. If that isn't true, then change the "1" in sh.Cells(sh.rows.count,1). However, you have to account for that fact as well, so it would be Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range Dim r1 As Range, sh As Worksheet Dim BaseWks as Worksheet, lastrow as Long Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) Set sh = BaseWks 'Set sh = ActiveSheet ' this is the summary sheet sPath = "D:\FAA 02 copied SPREADSHEETS\" sName = Dir(sPath & "*.xls?") Do While sName <> "" Set bk = Workbooks.Open(sPath & sName) Set r = bk.Worksheets(1).Range("A1").CurrentRegion ' I changed the 1 to a 2 to look in column B lastrow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Offset(1,0).Row Set r1 = sh.cells(lastrow, 1) r.Copy r1.PasteSpecial xlValues r1.PasteSpecial xlFormats bk.Close SaveChanges:=False sName = Dir() Loop End Sub ---------- FOLLOW-UP ---------- QUESTION: As a follow up question: 1. The first workbook that opens works fine and copies all the data including the header row. 2. All of the other workbooks open and copy/paste all of the data including the header row. 3. I would like to have the subsequent workbooks copy and paste rows 2...N, but not the header row. Thanks in advance,

• Answer:

  Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range Dim r1 As Range, sh As Worksheet Dim BaseWks as Worksheet, lastrow as Long Dim bFirst as Boolean bFirst = True Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) Set sh = BaseWks 'Set sh = ActiveSheet ' this is the summary sheet sPath = "D:\FAA 02 copied SPREADSHEETS\" sName = Dir(sPath & "*.xls?") Do While sName <> "" Set bk = Workbooks.Open(sPath & sName) Set r = bk.Worksheets(1).Range("A1").CurrentRegion if not bFirst then set r = r.offset(1,0).Resize(r.rows.count - 1, r.columns.count) else bFirst = False end if ' I changed the 1 to a 2 to look in column B lastrow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Offset(1,0).Row Set r1 = sh.cells(lastrow, 1) r.Copy r1.PasteSpecial xlValues r1.PasteSpecial xlFormats bk.Close SaveChanges:=False sName = Dir() Loop End Sub