CODEWITHSUNDEEP
excelvbapowerpoint
Charlie
Charlie

Reputation: 175

VBA: Only visible cells are getting pasted

I am trying to copy backend data from a chart in a slide and pasting it to an excel. The problem is only visible column(s) are getting pasted. On research I found out that while copying everything including hidden cells are indeed copied but for some reason only visible cells can be pasted. So I am struggling to find a way to paste everything. There is an option for unhiding columns before copying, but because any change in embedded excel in chart tends to stay, so trying to avoid that.

Also it seems only .PasteSpecial Paste:=xlPasteAll works with ppt chartdata. .PasteSpecial Paste:=xlPasteValuesAndNumberFormats; .PasteSpecial Paste:=xlPasteValues and others don't work.

Any help is much appreciated.

Code:

Dim rng As Excel.Range
Set rng = chtdat.Workbook.Sheets(1).Range("A1:Z17") 'Col C to G is hidden
'Have tried the following: UsedRange, CurrentRegion
 rng.Copy
        
 On Error Resume Next
    chtdat.Workbook.Close , False
 On Error GoTo 0
          
          If QCWbk.Sheets("QC-Sheet").Range("B3") = "" Then
          
             'QCWbk.Sheets("QC-Sheet").Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
             'QCWbk.Sheets("QC-Sheet").Range("B2").PasteSpecial Paste:=xlPasteValues
             
             'QCWbk.Sheets("QC-Sheet").Range("B2").PasteSpecial Paste:=xlPasteAll
              QCWbk.Sheets("QC-Sheet").Range("CheckThis").Sort key1:=QCWbk.Sheets("QC-Sheet").Range("B2:B17"), order1:=xlAscending, Header:=xlYes
             
             QCWbk.Save
             QCWbk.Close
             
          Else

Upvotes: 0

Views: 377

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

Try copy method, it will copy all value including hidden column, although I did not test for different workbook, it shall be working

Sheet1.Range("D5:F5").Copy Sheet1.Range("M2:O2")

If you only want copy visible cell, then including special cell function will work

Sheet1.Range("D5:F5").SpecialCells(xlCellTypeVisible).Copy  Sheet1.Range("M2:O2")

Upvotes: 0

Related Questions