Pasting error while copying multiple worksheets and pasting it in one worksheet using VBA ,

Question

I want to copy all rows and columns from various sheets and paste in an one worksheet one below the other .

But I am getting error . I tried using various example provided by the site . But none is working for me . Could please help me in resolving . I passing the worksheets in array .So I will get sheets in order.

I provided code where I am getting error . I am getting error in the pasting section . It is sayings that it should be either A1 or R1C1 . But I need to paste all sheets one below the another in one sheet.

For m = 1 To fnum


lastrow = tempws.Range("A" & ws.Rows.Count).End(xlUp).Row

ws(m).Cells.Copy tempws.Cells((lastrow + 1), 1)

Next m

tempws.Save

So you have a super sheet to be able to copy copy all rows and columns from various sheets? :P OR do you want to copy specific rows (because a row contains at least one column or columns) from various sheets and paste into one final sheet? — bonCodigo

Scott Holtzman Scott Holtzman · Accepted Answer · 2013-01-15T17:08:20

It's because you are trying to copy the ENTIRE range of cells of a worksheet into a section that is less than an entire sheet, on the main sheet. It won't work, because there aren't enough cells left. You need to size down your copy sheet to only capture the cells with data.

Something like this should be a good framework to get you started. You may need to tweak things to fit your data. Also, this assumes that all the data in your copy sheets starts from cell A1. Again, adjust if needed.

For me = 1 to fnum

    lastrow = tempws.Range("A" & tempws.Rows.Count).End(xlUp).Row 'i changed this to tempws, as you just had ws

    With ws(m)

        lastcopyrow = .Range("A" & .Rows.Count).End(xlup).Row
        lastcopycol = .Range("A" & .Columns.Count).End(xlToLeft).Column    

        .Range("A1",.cells(lastcopyrow, lastcopycol)).Copy tempws.Cells((lastrow + 1), 1)

    End With

Next m

Also, if you are getting a reference error due to A1 or R1C1 notation, you may want to just paste as values, instead of a straight copy / paste.

Pasting error while copying multiple worksheets and pasting it in one worksheet using VBA ,

1 Answers