0

I have code and it somewhat works. It does copy a worksheet from a workbook in a array of workbooks and then pastes the data to a master Workbook/Worksheet correctly. When it copies and pastes the another sheet to that master workbook/worksheet, it does find the last row and pastes that correctly. However, when it goes to the next workbook and sheet in the array, it copies the sheet okay, but does not paste it starting at the last empty row. Yes, I know this is simple, but not getting there.

If IsArray(FileArray) Then
   For i = LBound(FileArray) To UBound(FileArray)
      Set myBook = Workbooks.Open(FileArray(i))
      Sheets("Sheet1").Select
      Rows("1:4").Select
      Selection.Delete Shift:=xlUp
      For Each mySheet In myBook.Worksheets         
        mySheet.Range("A1").CurrentRegion.Copy
        Basebook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 
        0).PasteSpecial Paste:=xlPasteValues
      Next mySheet
      myBook.Close
   Next i
user2016566
  • 63
  • 2
  • 10
  • At what wrong row is it pasting? – DisplayName Apr 06 '19 at 05:47
  • `Basebook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial` should be `Basebook.Worksheets(1).Cells(Basebook.Worksheets(1).Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial`. You should also [avoid selecting or activating anything](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Tim Stack Apr 06 '19 at 07:03
  • And see **[THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920)** on how to find the last row – Siddharth Rout Apr 06 '19 at 07:33
  • @SiddharthRout well OP does use the right technique (although he is possibly better off populating a variable with the last row) – Tim Stack Apr 06 '19 at 07:42
  • @TimStack: That totally depends :) If all the rows have data till the same last row then getting the last row of Col A is enough else OP will have to use `.Find` Secondly yes, It is better to use a variable and everything properly qualified. Right now `Rows.Count` is not qualified. `Rows.Count` is referring to `Sheets("Sheet1") of myBook` and not `mySheet `. Better to use a structured code. – Siddharth Rout Apr 06 '19 at 07:47
  • Ah, of course, you're correct. And for your last remark, that's why I suggested the edited code in my initial comment :) – Tim Stack Apr 06 '19 at 07:54
  • Thanks to all of you. I did find the problem last night. The code actually does work fine. It seems to have been due to the data being in .xls format and not .xlsx. The sheet1 in each book was full to the last row. So both sheets from the first workbook correctly loaded. However, subsequent sheets from other books, overlayed the existing data rather than going to the end. When I converted to .xlsx everything was fine. I believe it was due to file size limitation??? – user2016566 Apr 06 '19 at 14:27
  • To answer your last question you should answer my first one “At what wrong row is it pasting?” – DisplayName Apr 06 '19 at 19:34

0 Answers0