finding the next empty cell so that it wont overwrite the previous pasted data

Question

I am having a problem to consolidate data from multiple worksheet into a summary worksheet. It is able to copy all the data except when the data is pasted it will overwrite the previous data. Example data in sheet A is pasted to recompile sheet starting from range A2. The problem is data in sheet B,C,D etc will also be pasted starting from range A2 causing it to overwrite each other. This is my code.

   Private Sub CommandButton2_Click()
   Dim Sheetname, myrange As String
   Dim A, noOfrows As Integer
   Dim startRow As Integer

   For i = 2 To Worksheets("Master Sheet").Cells.SpecialCells(xlCellTypeLastCell).Row

   Sheetname = Worksheets("Master Sheet").Cells(i, 27).Value'All the sheets that suppose to transfer to recompile sheet 
     noOfrows = Worksheets(Sheetname).Cells.SpecialCells(xlCellTypeLastCell).Row
     myrange = "A2:N" & CStr(noOfrows)
     Worksheets(Sheetname).Select
     Worksheets(Sheetname).Range(myrange).Select
     Selection.Copy
     Sheets("Recompile").Select         
     Range("A2").Select
     ActiveSheet.Paste

     Next i

     End Sub

Suggest using `Dim A, noOfrows As Long` as `Integer` isn't big enough if you use all the Rows available. Similarly, `Dim startRow As Long`. Doesn't solve your issue but will prevent another one — JustinJDavies, Nov 13 '13 at 09:26
@JustinJDavies: Surely you mean `Dim A As Long, noOfrows As Long`? Otherwise you're [not declaring `A` the way you think](http://stackoverflow.com/questions/11089040/difference-in-type-between-using-and-not-using-set-vba/11089684#11089684). — Jean-François Corbett, Nov 13 '13 at 10:25
You are correct. Except I actually meant: `Dim A As long`{newline}`Dim noOfrows As Long`. I would never write it from scratch as shown (the above is merely lazy editing) — JustinJDavies, Nov 13 '13 at 10:27

JustinJDavies · Answer 1 · 2013-11-13T10:28:10.447

You need to find the UsedRange in the "Recompile" sheet and paste into the range below that:

Something like:

Private Sub CopyData()

Dim A As Long
Dim noOfrows As Long
Dim startRow As Long
Dim i As Long

Dim control As Worksheet
Dim source As Worksheet
Dim target As Worksheet

Set control = Worksheets("Master Sheet")
Set target = Worksheets.Add

For i = 2 To control.UsedRange.Rows.Count
    ' the target worksheet for this row of data
    Set source = Worksheets(control.Cells(i, 1).Value) ' My example has this data in column A

    ' the address of a range with (number of rows - 1) for columns A:N
    source.Range("A2:N" & source.UsedRange.Rows.Count).Copy
    target.Range("A" & target.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row).PasteSpecial xlPasteValues
Next i

End Sub

Lots of information and tips here: http://www.rondebruin.nl/win/s3/win002.htm

I still got runtime error 9 subscript out of range and the data is still overwrite. — user2766881, Nov 13 '13 at 09:43
Try the example above - you will need to edit it a little to match your specific case. Note that the `xlTypeLastCell` becomes 'dirty' until you restart Excel, even if you delete cellsclear contents. This is why I make the target a new worksheet — JustinJDavies, Nov 13 '13 at 10:04

finding the next empty cell so that it wont overwrite the previous pasted data

1 Answers1