Only cells with values

[bones288]

Hello,

I've been experimenting with code:

Sub CopyColumnValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
If sh.UsedRange.Count > 1 Then
Last = Lastcol(DestSh)
With sh.Columns("AE:AE")
DestSh.Columns(Last + 1).Resize(, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

 

[Don Guillett]

Hello,

I've been experimenting with code:

Sub CopyColumnValues()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    If SheetExists("Master") = True Then
        MsgBox "The sheet Master already exist"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set DestSh = Worksheets.Add
    DestSh.Name = "Master"
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
            If sh.UsedRange.Count > 1 Then
                Last = Lastcol(DestSh)
                With sh.Columns("AE:AE")
                    DestSh.Columns(Last + 1).Resize(,_
                    .Columns.Count).Value = .Value
                End With
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Have a look in the vba help for specialcells

 

[Jim Cone]

Don,
I've found that SpecialCells inXL2010 can hang sometimes for minutes when trying to find
constants/formulas.
Until/unless MS issues a fix I would avoid using SpecialCells in XL2010.
I haven't found any pretty workarounds, only brute force...
reduce the range size if possible, use a variant to hold the range values and loop thru the variant
array.
'---
Jim Cone



"Don Guillett" <[email protected]>
wrote in message

Hello,

I've been experimenting with code:

Sub CopyColumnValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
If sh.UsedRange.Count > 1 Then
Last = Lastcol(DestSh)
With sh.Columns("AE:AE")
DestSh.Columns(Last + 1).Resize(, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Have a look in the vba help for specialcells

 

[Don Guillett]

Don,
I've found that SpecialCells inXL2010 can hang sometimes for minutes whentrying to find
constants/formulas.
Until/unless MS issues a fix I would avoid using SpecialCells in XL2010.
I haven't found any pretty workarounds, only brute force...
 reduce the range size if possible, use a variant to hold the range values and loop thru the variant
array.
'---
Jim Cone

"Don Guillett" <[email protected]>
wrote in message





Have a look in the vba help for specialcells

OP didn't mention version but good to know.... Only using 2010 when
client needs it