Select next empty cell in different sheet
Results 1 to 7 of 7

Thread: Select next empty cell in different sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Select next empty cell in different sheet

    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

  2. #2
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

    Try:

    Code:
    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
    -Red


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

    Thanks you, that works great!

    You can probably tell that I am new to this.

  4. #4
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

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


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

    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

  6. #6
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

    Try

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


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    New Member
    Join Date
    Mar 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Select next empty cell in different sheet

    Again, thank you. It works like a charm.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •