Select next empty cell in different sheet

[mach5j]

I am new to VBA and have been trying to figure out how to get a current sheet to copy and paste to a different sheet in the next empty cell. Here is what I have so far. It works nicely except that it copies over the existing data.

Sub ChooseBlankCells()
Dim MyName As String
MyName = Worksheets("Main").Range("E4").Value
If Worksheets("Main").Range("E4").Value = 1 Then
Range("E6").Copy Worksheets("Week1").Range("B5")
Range("E8").Copy Worksheets("Week1").Range("C5")
Range("I6").Copy Worksheets("Week1").Range("D5")
Range("M6").Copy Worksheets("Week1").Range("E5")
End If

End Sub

 

[Redwolfx]

Try:

Sub ChooseBlankCells()

Dim MyName As String
Dim LastRow as Long

Lastrow = Sheets("Week1").Range("B10000").End(xlup).Row +1

MyName = Worksheets("Main").Range("E4").Value

     If Worksheets("Main").Range("E4").Value = 1 Then
          Range("E6").Copy Worksheets("Week1").Range("B" & Lastrow)
          Range("E8").Copy Worksheets("Week1").Range("C" & Lastrow)
          Range("I6").Copy Worksheets("Week1").Range("D" & Lastrow)
          Range("M6").Copy Worksheets("Week1").Range("E" & Lastrow)
     End If

End Sub

 

[mach5j]

Thanks you, that works great!

You can probably tell that I am new to this.

 

[Redwolfx]

No problem at all, we were all new at one point. Welcome to the board.

 

[mach5j]

Ok, now I would like to add a text box to the same code above. I have played around with it but have not been successful. I'm not sure if this can be done but in place of Range("E4") I would like to have a text box that will perform the same functions and paste to the next available empty cell in Worksheets("Week1"). Any help would be greatly appreciated.


Sub ChooseBlankCells()Dim MyName As StringDim LastRow as LongLastrow = Sheets("Week1").Range("B10000").End(xlup).Row +1MyName = Worksheets("Main").Range("E4").Value If Worksheets("Main").Range("E4").Value = 1 Then Range("E6").Copy Worksheets("Week1").Range("B" & Lastrow) Range("E8").Copy Worksheets("Week1").Range("C" & Lastrow) Range("I6").Copy Worksheets("Week1").Range("D" & Lastrow) Range("M6").Copy Worksheets("Week1").Range("E" & Lastrow) End IfEnd Sub</pre>

 

[Redwolfx]

Try

Worksheets("main").textbox1.value =1

 

[mach5j]

Again, thank you. It works like a charm.