Help with VBA Copy Code

[RTowles]

would like to get help on a code that I was able to find that I think should help that does not change the formatting of my created excel sheet.

Can someone please advise why the code is not working when I click Run Macros.

Sheet1.Range - I turned this to F3 to test it out. I tried using F:F, but I'm not sure if this code includes blank in the data
Sheet2.Range - A2 is blank and the copied information should have populated to Sheet2 A2 location
if Status = TRUE then copy the entire row and pastecell, why is this not working?

Sub CopyOverBudgetRecords()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Set StatusCol = Sheet1.Range("F3")

For Each Status In StatusCol

If Sheet2.Range("A2") = "" Then
Set PasteCell = Sheet2.Range("A2")
Else
Set PasteCell = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
End If
'If Status = "TRUE" Then Status.EntireRow.Copy PasteCell
Next Status

End Sub

 

[Logit]

I just recently tried to copy Checkboxes and related data from one sheet to another in one of my small projects. I learned
that Excel / VBA will not copy a checkbox. The workaround (which isn't really a workaround) is to do away with the checkboxes
and rely on a simple "X" as a replacement.

 

[Jon Peltier MVP]

Sure you can copy checkboxes.

In my example, Sheet1 has a checkbox on cell N5, with a linked cell of N5.

This copies the checkbox to Sheet2 cell N5

Sub CopyCheckbox()
  Worksheets("Sheet1").Shapes("Check Box 1").Copy
  Application.Goto Worksheets("Sheet2").Range("N5")
  ActiveSheet.Paste
End Sub

Either of these copies the cells below the checkbox (which includes the checkbox) and pastes them into Sheet2:

Sub CopyCheckboxAndCells1()
  Worksheets("Sheet1").Range("N5:O6").Copy Worksheets("Sheet2").Range("N5")
End Sub

Sub CopyCheckboxAndCells2()
  Worksheets("Sheet1").Range("N5:O6").Copy
  Application.Goto Worksheets("Sheet2").Range("N5")
  ActiveSheet.Paste
End Sub

 

[Logit]

Very good. Wish I had that information a few weeks ago.

 

[RTowles]

Okay, I'm starting to think I'm slow. I input each of those codes in the module 1 on my sheet and none worked. I actually got an error that said Run-time error '9' subscript out of range. Weird. So I recopied your script and didn't change any data and I get the same error. Why are the gods frowning down upon me?

 

[Logit]

Make certain your sheet names are spelled correctly in the macro.

 

[Jon Peltier MVP]

If you don't have a checkbox named "Check Box 1", you will see that error in the first code sample.

If you don't have worksheets named "Sheet1" and "Sheet2", you will see that error in all three code samples.

Note that I'm using a Form checkbox. The first code sample works for an ActiveX checkbox, but the second and third do not.

You should probably avoid ActiveX controls. They're a bit newer and have more formatting options, but they're a bit flaky and only work in Windows. Also I read that Microsoft is deprecating them soon.

Form controls work in Windows and Mac. Neither work in Excel for Web. If you have M365, you should check out the new worksheet checkbox (Forms ribbon tab > Controls > Checkbox). These are really cool, they're not an object (shape) floating above the grid. They're like a cell formatting option that is an unchecked box for a cell with a FALSE value and a checked box for a cell with a TRUE value. They might not be generally available for most corporate licenses yet.