0

I have been working on a code to copy the data from one specific range(always the same) and paste in another spreadsheet always in the row below. So basically, it starts pasting on row 11, but if I run again it will paste on the row 12 and there it goes.. The code has been working fine, but there is only one problem. It identifies the next empty row(to paste) based on the value of the column AP, but i want it to identify based on the values of all the columns between AP:BA. Thus, if there is any value on those cells, it should copy on the row below, not only if there is a value on AP. Does someone know how to change my code in order to solve this problem? Thank You very much

Sub Copy_Shanghai()    
Dim count As Integer

count = 11     

Do While Worksheets("Time Evolution").Range("AP" & count).Value <> ""


'<>"" means "is not empty", as long as this happens we go down looking for empty cell
    count = count + 1
  Loop
  'Now count is row with first empty cell outside of top 10 rows in column C

  Worksheets("Fill").Range("E5:P5").Copy
  Worksheets("Time Evolution").Range("AP" & count).PasteSpecial xlPasteValues

End Sub
Community
  • 1
  • 1
user7004
  • 185
  • 2
  • 3
  • 11
  • Not completely sure I follow? Do you mean you want it to continue while none of your criteria equal 0? That being the case, you could do a concatenated value and compare that to 0 - without making any major changes to your current code – Trum Jun 30 '15 at 13:58
  • No, i will give an example. If the value of AP11 is not empty, the vba will run ok and paste the on the row below. However, if AP11 is empty, the vba does not recognize and paste it on the wrong row. What I need is that IF there is any non empty cell on row 11(from AP to BA), it recognizes and paste on the row below. Is it clearer now? – user7004 Jun 30 '15 at 14:04
  • It was as I gathered. You could simply do something like: Range("AP" & count).Value & Range("AQ" & count).value & Range("AR" & count).value = "" This will count all of the concatenated strings. – Trum Jun 30 '15 at 14:19
  • please see this [LINK](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) to get information about how to get correct used range. – Dubison Jun 30 '15 at 14:27

0 Answers0