Copying data from another excel workbook - instead of one sheet it's copying all of them

Question

I have several Excel files that I want to merge. Each file has 17 sheets, they are named 00, 01, 02, 03, etc.. Now, I open each of the workbook and try to copy the data into one file, that also has 16 sheets, on sheet x I want to have information from all of the other workbooks from sheet x.

Now, I open the file and for each file I have this code:

For i = 0 To 16

  Workbooks(nazwaPliku).Activate
  zakladka = Right("0" & CStr(i), 2)
  Sheets(zakladka).Activate
  ileWierszy = Application.WorksheetFunction.Max(Sheets(zakladka).Range("B:B"))
  wierszMin = Application.WorksheetFunction.Match("1", Sheets(zakladka).Range("B:B"), 0)
  zakresDoKop = "A" & wierszMin + 1 & ":" & "I" & wierszMin + ileWierszy + 1 '1 wiecej dla bezpieczenstwa
  Sheets(zakladka).Range(zakresDoKop).Resize(ileWierszy, 1).Value = rok & "_" & czesc
  Sheets(zakladka).Range(zakresDoKop).Copy
  ThisWorkbook.Sheets(zakladka).Activate
  ThisWorkbook.Sheets(zakladka).Range("A" & wsk(i)).PasteSpecial

  wsk(i) = wsk(i) + ileWierszy + 2
Next i

For the first file everything is fine (wsk = 2 for all the sheets), but when I open the next workbook, something strange is happening. For example, when i=2, the code is copying data from sheets 02 - 16 and pasting them in thisworkboook. What's more, I've tested by deleting sheet 16 in my workbook and when i <> 16, an error occurred (as it was lacking one sheet). I think maybe I don't quite understand the loop here?

Any ideas? I know how i could write it differently but since i spent last 4 hours trying to figure things out, I would really like to know why is this happening.

Is all your data in ascending order so that Max always gets the last row? — Lance Roberts

ARich ARich · Accepted Answer · 2014-01-30T19:33:58

Try something like this:

Untested

Dim wb As Workbook
Dim ws As Worksheet
Dim desWs As Worksheet
Dim firstRow As Long
Dim lastRow As Long
Dim lastColumn As Long
Dim pasteRow As Long

'I'm assuming you're looping through your workbooks... so set each open workbook to _
'the wb variable once you've opened it/when you open it.

'This will loop through each sheet in the workbook you just opened and paste the _
'values in the corresponding ThisWorkbook sheet.
For Each ws In wb.Sheets
    'Since the sheet names in ThisWorkbook are the same as in the other wb, we can _
    'use the current worksheet name for the destination sheet. (ws.Name)
    set desWs = ThisWorkbook.Sheets(ws.Name)
    firstRow = ws.Range("B:B").Find("*", searchdirection:=xlNext).Row + 1
    lastRow = ws.Range("B:B").Find("*", searchdirection:=xlPrevious).Row
    lastColumn = ws.Rows("1:1").Find("*", searchdirection:=xlNext).Column
    pasteRow = desWs.Range("A:A").Find("*", searchdirection:=xlPrevious).Row + 1
    desWs.Range(desWs.Cells(pasteRow, 1).Resize(lastRow-firstRow, lastColumn) = _
        ws.Range(ws.Cells(firstRow, 1), ws.Cells(lastRow, lastColumn)).Value
Next ws

Remember, this method only copies values, not formatting.

Copying data from another excel workbook - instead of one sheet it's copying all of them

1 Answers