.PasteSpecial xlPasteValues is clearing boolean values from cell

[LlebKcir]

        j = 2       ' Reset j back to 2.
                    ' Sets starting point for copy/paste of data to correct Cell.
                    Set rFoundCell = Worksheets("overview").Range("G1")

               ' Starts for loop to search for 'start' vaule on the Overview Worksheet in the Fire Detect column.
               For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("g1:g200"), start)
               ' Finds the 'start' value on the Overview Worksheet in the Fire Detect column.
               Set rFoundCell = Worksheets("overview").Columns(7).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

                     ' Start of the copy/paste data from Overview to new Group # Worksheet in a transpose form.
                     With rFoundCell
                         ' The offset will move the source from column G to column AM, Source is now column G, not A.  Column G = 0, H = 1, F = -1
                         Worksheets("overview").Range(rFoundCell.Address).Offset(, 32).Copy
                         Worksheets("group " & start).Activate
                         '                           .Cells([row], [column]).   .Cells(109, j) moves down to Row 109.
                         Worksheets("group " & start).Cells(109, j).PasteSpecial xlPasteValues
                         j = j + 1

                     End With

               Next lCount

Using the above code does what I need, except when it comes to boolean values. All boolean values are converted to raw text. That breaks most if not all of my formulas in the workbook.

What can I replace the .PasteSpecial xlPasteValues with to not change the destination formatting, but keep the boolean source value?

 

[6StringJazzer]

A couple of notes. First of all

Range(rFoundCell.Address)

is a very roundabout way to refer to the cell and is exactly the same as

rFoundCell

since you are in the same sheet, which is what you should use.

Second, I am surprised at the behavior you are seeing. However, the code below should be equivalent and not cause this problem:

        j = 2       ' Reset j back to 2.
                    ' Sets starting point for copy/paste of data to correct Cell.
                    Set rFoundCell = Worksheets("overview").Range("G1")

               ' Starts for loop to search for 'start' vaule on the Overview Worksheet in the Fire Detect column.
               For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("g1:g200"), start)
               ' Finds the 'start' value on the Overview Worksheet in the Fire Detect column.
               Set rFoundCell = Worksheets("overview").Columns(7).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

                     ' Start of the copy/paste data from Overview to new Group # Worksheet in a transpose form.
                     With rFoundCell
                         ' The offset will move the source from column G to column AM, Source is now column G, not A.  Column G = 0, H = 1, F = -1
                         Worksheets("group " & Start).Cells(109, j).Value = rFoundCell.Offset(, 32).Value

                         j = j + 1

                     End With

               Next lCount

 

[LlebKcir]

6StringJazzer, am I reading this to have: Worksheets("group " & Start).Cells(109, j).Value as the source and rFoundCell.Offset(, 32).Value as the destination?

Also note that the source worksheet and destination worksheet are not the same.

Source = Overview
Destination = "group" & start such that the new worksheet(s) created by earlier code are numbered in order Group 1, Group 2, etc... That is what I am using the Set rFoundCell = Worksheets("overview").Range("G1") for. That will locate the # index in column G to identify the destination worksheet.

Also using .value = .value will that keep the destination formatting without breaking the boolean source value?

Sorry just trying to learn all of these ins and outs. im horrid at coding. Thank you.

 

[LlebKcir]

6StringJazzer, I hope to hear back from you before I change the code. I'd really like to better understand what it is telling me so I can add comments. I only touch coding maybe 1 time a year.

I shall return tomorrow morning and check for feedback.

Thank you again for the code section.

 

[6StringJazzer]

6StringJazzer, am I reading this to have: Worksheets("group " & Start).Cells(109, j).Value as the source and rFoundCell.Offset(, 32).Value as the destination?

No. It's the other way around. Note that this uses an assignment statement.

Also note that the source worksheet and destination worksheet are not the same.

Noted. This is taken into account.

Also using .value = .value will that keep the destination formatting without breaking the boolean source value?

That is my expectation. But then again I do not know why the original code did not preserve the boolean value. You may have something going on in the data itself that we can't diagnose by playing with the code.

 

[LlebKcir]

Many thanks for the detailed reply. That really helps and is greatly appreciated. That worked and now the formulas I have on the destination worksheets run as expected.

If you have time can we address another question?

With this working code, I have some data that I only want to copy over the FALSE values, if they are TRUE I want to iterate the loop, but not copy that cell from source to destination.

I tried the following and it completely broke all of the copy/paste code.

 j = 2       ' Reset j back to 2.
                    ' Sets starting point for copy/paste of data to correct Cell.
                    Set rFoundCell = Worksheets("overview").Range("G1")

               ' Starts for loop to search for 'start' vaule on the Overview Worksheet in the Vault Temp column.
               For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("g1:g200"), start)
               ' Finds the 'start' value on the Overview Worksheet in the Vault Temp column.
               Set rFoundCell = Worksheets("overview").Columns(7).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)

                     ' Start of the copy/paste data from Overview to new Group # Worksheet in a transpose form.
                     With rFoundCell
                         ' The offset will move the source from column G to column AJ, Source is now column G, not A.  Column G = 0, H = 1, F = -1
                         ' testing IF/ELSE statement to exit WITH loop and itereate when boolean = true <28 Jan. 2022: RLB>
                         ' the folllowing If/Else loop breaks the code.  Creates 110 worksheets without data. <30 Jan. 2022: RLB>
                                'If Worksheets("overview").Range(rFoundCell.Address).Offset(, 35).Value = False Then j = j + 1
                                'Else

                         ' The offset will move the source from column G to column AJ, Source is now column G, not A.  Column G = 0, H = 1, F = -1
                         'Worksheets("overview").Range(rFoundCell.Address).Offset(, 29).Copy
                         Worksheets("group " & start).Activate
                         '                           .Cells([row], [column]).   .Cells(113, j) moves down to Row 113.
                         'Worksheets("group " & start).Cells(113, j).PasteSpecial xlPasteValues
                         ' following code from MrExcel: 6StringJazzer
                         Worksheets("group " & start).Cells(113, j).Value = rFoundCell.Offset(, 29).Value

                         j = j + 1

                     End With

               Next lCount

Currently I have the If/Else loop commented out. Tried with both negative and positive If = and neither worked as desired. The current If statement you see allowed the copy of the worksheets, but did not allow any data to be copied from Overview to their Group # worksheets.

Thank you again for the help with fixing my issue on the boolean values.

 

[6StringJazzer]

First you do not need to do this unless you want your user to see this sheet for some reason. It will slow down the code an isn't needed to be able to operate on the sheet.

Worksheets("group " & start).Activate

I only want to copy over the FALSE values, if they are TRUE I want to iterate the loop, but not copy that cell from source to destination.

I tried the following

I don't see any of that in this code. This just looks like the original code with my change in it. For what you described, this modification should do it.

                         If Not rFoundCell.Offset(, 29).Value Then
                            Worksheets("group " & start).Cells(113, j).Value = False
                         End If

This time there is no need to copy the actual cell value because at the time you do the copy you already know it is False.

 

[LlebKcir]

First you do not need to do this unless you want your user to see this sheet for some reason. It will slow down the code an isn't needed to be able to operate on the sheet.

Worksheets("group " & start).Activate

Can I fully remove that line of code from the loops of this type of code, not just with the If Not line you provided.

That line works fantastic. Thank you for both the information as well as the updated code.

Sorry, I forgot to uncomment the IF/Else statement I had in my loop. It is there just commented out.

 

[6StringJazzer]

The answer to this is going to be a little long-winded.

In code the best practice is to explicitly identify which worksheet you are operating on. If you omit the worksheet qualification like this

Range("A1") = "New value"

two different things could happen depending on where the code is located.
1. If this code is in a Worksheet module, the Range reference will be for that worksheet
2. If this code is in a Standard Module (like Module1), the Range reference will be for the active worksheet
For case #2 people like to do things like this:

Worksheets("group " & start).Activate
Range("A1") = "New value"

to contrive which sheet is active. However, sometimes these two lines of code are in quite different places, maybe not even in the same module, and cause many headaches when a bug comes up. That is why the best practice is to avoid this default in a Standard Module and do this

Worksheets("group " & start).Range("A1") = "New value"

In other cases, like yours, the Activate code is not actually needed for the subsequent code, and serves only to show that sheet to the user as the code executes. Unless you have a specific reason to do so, this should be avoided because it will look weird to the user, and take up a relatively large amount of execution time, slowing down the code.

 

[LlebKcir]

Thank you for the detailed feedback. That helps very much.

now to figure out why my hyperlinks are not longer populating :D

VBScript to make internal & external hyperlinks no longer working????

' ====================================================== ' Begin code to Hyperlink process on Overview Worksheet. ' ====================================================== ' Replaced message box with Max Function used on Column G (Group Number) on the Overview Worksheet...

www.mrexcel.com