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