Results 1 to 1 of 1

Thread: (Updated)pasting text from one page into the first empty cell of another page

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2005
    Location
    Cape Cod, MA
    Posts
    1

    (Updated)pasting text from one page into the first empty cell of another page

    Here is a new code snippet that I am trying. Seems to get me closer, but I get an error.
    VB Code:
    1. Private Sub Check33_Click()
    2.    
    3.    Dim FirstEmpty As Range
    4.    Sheet6.Activate
    5.    Set FirstEmpty = Cells(Rows.Count, "B").End(xlUp)(2)
    6.      
    7.    If Sheet2.Range("K23").Value = True Then
    8.    ActiveWorkbook.Sheets("Sheet6").Range(FirstEmpty).Value = Sheet2.Range("B23").Value
    9.    End If
    10.    
    11. End Sub

    This line gives me a 'Subscript out of range' error
    VB Code:
    1. ActiveWorkbook.Sheets("Sheet6").Range(FirstEmpty).Value = Sheet2.Range("B23").Value

    One thing I did not mention before, I am trying to paste from merged cells to merged cells. I want the first empty cell in the "B" col on sheet6


    Here is what I am trying to do. I have cells with text. There is a cell next to each with a check box. When the checkbox is checked I want the text to be pasted into the first empty cell of a different sheet.
    So, the text in cell B3 on Sheet1 is filled in, the checkbox is clicked, the code reads the value in the linked cell, if True then the text is posted to the first empty cell on SheetA(at least, this what I would like to have happen) The code below is what I was able to come up with after searching this forum and with my old knowledge from VB4. I believe I am close since the sheet does get activated when the box is checked. I am able to get it to post to a specified cell by using the code in the second sample, but I have over 100 checkboxes and I don't want a Sub for each one. Hopefully this is clear enough for someone to be able to help me. Thanks



    VB Code:
    1. Private Sub Check33_Click()
    2.   Dim lastRow As Long
    3.     ThisWorkbook.Activate
    4.     SheetA.Activate
    5.     'lastrow is the last row without data
    6.     lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
    7.    
    8.     If Sheet2.Range("K23").Value = True Then
    9.        SheetA.Range("lastRow").Value = Sheet2.Range("B23").Value
    10.     End If
    11. End Sub

    VB Code:
    1. Private Sub Check79_Click()
    2.     If Sheet3.Range("K13").Value = True Then
    3.        SheetA.Range("B101").Value = Sheet3.Range("B13").Value
    4.     End If
    5. End Sub
    Last edited by Kingman; May 5th, 2005 at 01:34 PM. Reason: updated info

Posting Permissions

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



Click Here to Expand Forum to Full Width