Forum Discussion

Kamal_chennai's avatar
Kamal_chennai
Contributor
9 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.Spe...
shankar_r's avatar
shankar_r
Community Hero
9 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.