Forum Discussion

Kamal_chennai's avatar
Kamal_chennai
Contributor
8 years ago

How to copy and paste only visible cells from Excel using vbscript

Hi,

I want to copy only visible cells by using vbs. not vba. In Img 1 Country column is hidden but while pasting the visible cell from sheet1 to sheet2 its showing country column.

Selection.SpecialCells(xlCellTypeVisible).Select

This code is not working in vbs.


Img 1:

upload_2017-4-6_16-43-36.png

Img 2:

upload_2017-4-6_17-35-24.png


Please let me know if there is any alternate way to do that.


Thanks,
Kamal

1 Reply

Sort By
  • shankar_r's avatar
    shankar_r
    Community Hero
    8 years ago

    Hi,

     

    Below code will copy only the visible cells from Sheet1 Range("A1:F1") to Sheet2 Range("A2:F2").

     

    Sub ReadDataFromExcel
  Dim Excel

  Set Excel = Sys.OleObject("Excel.Application")
  Excel.Workbooks.Open("D:\Users\Documents\Testss.xlsx")

  Excel.ActiveWorkbook.Sheets.Item("Sheet1").Range("A1:F1").SpecialCells(12).Copy(Excel.ActiveWorkbook.Sheets.Item("Sheet2").Range("A2:F2"))
  Excel.ActiveWorkbook.Save
  Excel.Quit
End Sub

     You can customize this code as per your requirement.