b4tmast

11-09-2016, 09:00 AM

First, I am a newbie and haven't got a clue what I'm doing. I appreciate any and all help.

I have a small table (Excel 2013) with 2 columns and from 21-24 rows with command buttons to the left on each row. I am trying to write a script so that when the checkbox to the left is checked it will copy the data from the two adjacent cells (columns B & C) and paste to a new table (column H2) - just two cells of data. The paste has to be of Values because the cells they're copying from have formulas.

When it goes to the new table it has to be able to know how many rows are there, if any, and then drop down to paste. When it starts, there are no rows of data. So the first paste will be the first row in the new table.

So far, miraculously, I got it to copy and paste - formulas though. Also, it copies whether or not, there is a check in the check box. So I'm not using my loops right, nor the code...

I have not added code for all the check boxes because I first need to get it to do the basics - after that I'll add the code with the other check boxes.

I'm sure this is simple to some, but, completely overwhelming to me. I've made it thus far and am ready to pull my hair out. Any and all help would be appreciated. Thank you:

Private Sub EstablishTagList()

Dim ws As Worksheet

Dim i As Long

' Initalize variables

Set ws = Sheets("DoorTags")

' Clear the contents of the Active Page

ws.Range("H2:I25").Clear

With ws

ws.Select

' Find the last row of data in the new Tag list.

Lr = .Range("H2").End(xlDown).Row

' Loop through each row

For i = 2 To Lr

' Decide if to copy based on command buttons in Column A

If chkMaster.Value = True Then 'Once I figure out how to do this I will add all the other check boxes with If Conditionals.

.Cells(i, 2).Resize(1, 2).Copy

'Application.CutCopyMode = False

Sheets("DoorTags").Select

ActiveSheet.Cells(i, 8).PasteSpecial xlPasteValues

End If

Next

End With

End Sub

I have a small table (Excel 2013) with 2 columns and from 21-24 rows with command buttons to the left on each row. I am trying to write a script so that when the checkbox to the left is checked it will copy the data from the two adjacent cells (columns B & C) and paste to a new table (column H2) - just two cells of data. The paste has to be of Values because the cells they're copying from have formulas.

When it goes to the new table it has to be able to know how many rows are there, if any, and then drop down to paste. When it starts, there are no rows of data. So the first paste will be the first row in the new table.

So far, miraculously, I got it to copy and paste - formulas though. Also, it copies whether or not, there is a check in the check box. So I'm not using my loops right, nor the code...

I have not added code for all the check boxes because I first need to get it to do the basics - after that I'll add the code with the other check boxes.

I'm sure this is simple to some, but, completely overwhelming to me. I've made it thus far and am ready to pull my hair out. Any and all help would be appreciated. Thank you:

Private Sub EstablishTagList()

Dim ws As Worksheet

Dim i As Long

' Initalize variables

Set ws = Sheets("DoorTags")

' Clear the contents of the Active Page

ws.Range("H2:I25").Clear

With ws

ws.Select

' Find the last row of data in the new Tag list.

Lr = .Range("H2").End(xlDown).Row

' Loop through each row

For i = 2 To Lr

' Decide if to copy based on command buttons in Column A

If chkMaster.Value = True Then 'Once I figure out how to do this I will add all the other check boxes with If Conditionals.

.Cells(i, 2).Resize(1, 2).Copy

'Application.CutCopyMode = False

Sheets("DoorTags").Select

ActiveSheet.Cells(i, 8).PasteSpecial xlPasteValues

End If

Next

End With

End Sub