Replies

Thanks. I thought it would be something simple. Duh.

Explanation in attachment.

Happy Thanksgiving!

Explanation in attachment.

Happy Thanksgiving!

Here’s a solution without VBA. Your list will have to be sorted by case, and you need to use a summary area on the sheet with a unique list of cases. Unwanted columns can be hidden.

Ken

Here’s a solution without VBA. Your list will have to be sorted by case, and you need to use a summary area on the sheet with a unique list of cases. Unwanted columns can be hidden.

Ken

This is an old thread, but probably still an occasional issue for some. I have a workbook that would not let me copy or insert a new sheet, but always crashed due to the worksheet code names being too long. Neither could I rename the sheet code names in VBA as anything I renamed it to was reported as invalid.

I fixed the problem by saving the wookbood in 5.0/95 format…it renamed all the sheet code names for me. There was of course a warning that some formatting would be lost, but that was a trivial problem in this case.

This is an old thread, but probably still an occasional issue for some. I have a workbook that would not let me copy or insert a new sheet, but always crashed due to the worksheet code names being too long. Neither could I rename the sheet code names in VBA as anything I renamed it to was reported as invalid.

I fixed the problem by saving the wookbood in 5.0/95 format…it renamed all the sheet code names for me. There was of course a warning that some formatting would be lost, but that was a trivial problem in this case.

[indent]

---

That won’t work if the area being copied contains formulas. The formulas will refer back to the sheet they were copied form, not the sheet they are pasted to.

---

[/indent]

Here’s a couple of macros to work around this problem if cell references don’t need to be adjusted. Select the area to copy and run ApostropheInsert. After copying and pasting run ApostropheRemove while cells are still selected. Run it again on the original selection to restore formulas

Ken

Sub ApostropheInsert()
Dim rngCell As Range
For Each rngCell In Selection
    If rngCell.HasFormula Then
      rngCell.Value = "'" & rngCell.Formula
    End If
Next
End Sub

Sub ApostropheRemove()
Dim rngCell As Range
For Each rngCell In Selection
    If Left(rngCell.Value, 1) = "=" Then
      rngCell.Formula = rngCell.Value
    End If
Next
End Sub

[indent]

---

That won’t work if the area being copied contains formulas. The formulas will refer back to the sheet they were copied form, not the sheet they are pasted to.

---

[/indent]

Here’s a couple of macros to work around this problem if cell references don’t need to be adjusted. Select the area to copy and run ApostropheInsert. After copying and pasting run ApostropheRemove while cells are still selected. Run it again on the original selection to restore formulas

Ken

Sub ApostropheInsert()
Dim rngCell As Range
For Each rngCell In Selection
    If rngCell.HasFormula Then
      rngCell.Value = "'" & rngCell.Formula
    End If
Next
End Sub

Sub ApostropheRemove()
Dim rngCell As Range
For Each rngCell In Selection
    If Left(rngCell.Value, 1) = "=" Then
      rngCell.Formula = rngCell.Value
    End If
Next
End Sub

Here’s a non-macro solution that will insert a blank row after each group of identical SSN’s.

Easier to show than explain.

Ken

Here’s a non-macro solution that will insert a blank row after each group of identical SSN’s.

Easier to show than explain.

Ken

Alan,

Here’s the macro I’ve been using. Seems to work OK on discontiguous selections

Sub PasteValues()
Dim rngFormulas As Range
Dim rngCell As Range
Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
For Each rngCell In rngFormulas
rngCell.Formula = rngCell.Value
Next
Set rngFormulas = Nothing
End Sub

Ken

Alan,

Here’s the macro I’ve been using. Seems to work OK on discontiguous selections

Sub PasteValues()
Dim rngFormulas As Range
Dim rngCell As Range
Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
For Each rngCell In rngFormulas
rngCell.Formula = rngCell.Value
Next
Set rngFormulas = Nothing
End Sub

Ken

Here’s an alternative way without arrays.

Ken

Here’s an alternative way without arrays.

Ken