Here is a new code snippet that I am trying. Seems to get me closer, but I get an error.
VB Code:
Private Sub Check33_Click() Dim FirstEmpty As Range Sheet6.Activate Set FirstEmpty = Cells(Rows.Count, "B").End(xlUp)(2) If Sheet2.Range("K23").Value = True Then ActiveWorkbook.Sheets("Sheet6").Range(FirstEmpty).Value = Sheet2.Range("B23").Value End If End Sub
This line gives me a 'Subscript out of range' error
VB Code:
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:
Private Sub Check33_Click() Dim lastRow As Long ThisWorkbook.Activate SheetA.Activate 'lastrow is the last row without data lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 If Sheet2.Range("K23").Value = True Then SheetA.Range("lastRow").Value = Sheet2.Range("B23").Value End If End Sub
VB Code:
Private Sub Check79_Click() If Sheet3.Range("K13").Value = True Then SheetA.Range("B101").Value = Sheet3.Range("B13").Value End If End Sub


Reply With Quote