Copy specific cells to another sheet BUT if there is "text" in destination, try another spot, or another

[Luvlimum]

I have trowelled through as many posts to see if this has been addressed and I cannot seem to find anything that fits. Please note I am very new to VB and I am learning. In saying that ...

I would like to copy Cells C6:D13 FROM Sheet1
to C6:D13 of Sheet 2
BUT If there is already text in C6:D13 of Sheet2, I would like it to try pasting it to C15:D15 of Sheet2
IF there is already text in C15:D15 of Sheet 2, then I want it to look at C24:D24 of Sheet2
IF there is already text in C24:D24 of Sheet2, then try C33:D33 of Sheet2 and so on (but only a couple more times)
...
Can any help me with a code for this.
Thanks in advance - much appreciated

 

[Alex Blakenburg]

That is something that is used in nearly every piece of code.
You just need to be searching for VBA Last Row.
Here is one example: VBA Tutorial: Find the Last Row, Column, or Cell in Excel

In your case you want to leave a blank line and then use the next cell so it would be last row + 2

Sub SetRngToLastRow()

    Dim destSht As Worksheet
    Dim destRng As Range
    Dim lastRow As Long
    
    Set destSht = Worksheets("Sheet2")
    lastRow = destSht.Cells(Rows.Count, "C").End(xlUp).Row

    If lastRow < 6 Then
        Set destRng = destSht.Range("C6")               ' Preferred location C6
    Else
        Set destRng = destSht.Range("C" & lastRow + 2)  ' Add 2 to last used row to leave a blank line
    End If

    Worksheets("Sheet1").Range("C6:D13").Copy destRng
    
End Sub

 

[Luvlimum]

That is something that is used in nearly every piece of code.
You just need to be searching for VBA Last Row.
Here is one example: VBA Tutorial: Find the Last Row, Column, or Cell in Excel

In your case you want to leave a blank line and then use the next cell so it would be last row + 2

Sub SetRngToLastRow()

    Dim destSht As Worksheet
    Dim destRng As Range
    Dim lastRow As Long
   
    Set destSht = Worksheets("Sheet2")
    lastRow = destSht.Cells(Rows.Count, "C").End(xlUp).Row

    If lastRow < 6 Then
        Set destRng = destSht.Range("C6")               ' Preferred location C6
    Else
        Set destRng = destSht.Range("C" & lastRow + 2)  ' Add 2 to last used row to leave a blank line
    End If

    Worksheets("Sheet1").Range("C6:D13").Copy destRng
   
End Sub

Thank you for this. I am getting a runtime error 9, subscript out of range? On Dim destSht As Worksheet - I am very new to this so I apologise if this is a simple thing to sort.

 

[Alex Blakenburg]

Thank you for this. I am getting a runtime error 9, subscript out of range? On Dim destSht As Worksheet - I am very new to this so I apologise if this is a simple thing to sort.

Are you sure the error is not on this line ?

    Set destSht = Worksheets("Sheet2")

I was working off your original description. If you are getting an error on that line it would indicate that you have no sheet called Sheet2.
Change the name to whatever the sheet name is that you referred to as being Sheet2.

 

[Luvlimum]

Are you sure the error is not on this line ?

    Set destSht = Worksheets("Sheet2")

I was working off your original description. If you are getting an error on that line it would indicate that you have no sheet called Sheet2.
Change the name to whatever the sheet name is that you referred to as being Sheet2.

You brilliant man! I had renamed the sheets. One for was Sheet2 "Main", the source sheet was Sheet1 "Data". Once I changed the names from ("Sheet2") to ("Main") - it worked beautifully (told you I was new at this).

 

[Luvlimum]

May I ask one more question? Once these pasted cells have been updated, I will need to move them again, across to another column on the same sheet. I think I can use the code to do this with a couple of tweeks, however, the difference will be I only want to move the group of cells that my MOUSE is focused on. So If my mouse is on C6 OF C6:D13, I will want to move those cells over to another column, once this order has been approved. Is that going to be difficult?

 

[Luvlimum]

You brilliant man! I had renamed the sheets. One for was Sheet2 "Main", the source sheet was Sheet1 "Data". Once I changed the names from ("Sheet2") to ("Main") - it worked beautifully (told you I was new at this).

May I ask one more question? Once these pasted cells have been updated, I will need to move them again, across to another column on the same sheet. I think I can use the code to do this with a couple of tweeks, however, the difference will be I only want to move the group of cells that my MOUSE is focused on. So If my mouse is on C6 OF C6:D13, I will want to move those cells over to another column, once this order has been approved. Is that going to be difficult?

 

[Alex Blakenburg]

In principle all you would need to do is change this

Worksheets("Sheet1").Range("C6:D13").Copy destRng

To this

Selection.Copy destRng

 

[Luvlimum]

In principle all you would need to do is change this

Worksheets("Sheet1").Range("C6:D13").Copy destRng

To this

Selection.Copy destRng

Actually I would need to pickup C6:D13 and move it over to H6:D13 OR if that is taken, then move it down to H14:D24 (like previously but on the same sheet).

Because I was wondering if I could group the C6:D13 and name the range using CTL F3 and then referring to that named range in the VBA code?

Lx

 

[Alex Blakenburg]

The code I gave you in post #2 can be easily adapted to all those scenarios.
For a range name (include the quotes around the range name)
Range("Range_Name").Copy destRng