13 September 2018

MS Excel VBA - Copy hidden Excel worksheet contents to new workbook

Was trying to copy the contents of a hidden worksheet in an Excel to a totally new workbook and edit it via VBA and faced several issues.

     - One can't copy contents from a hidden worksheet. The worksheet must be visible in order for it to be copied.
     - After making it visible, and copying it, there were problems making it hidden again as the current active workbook is the new one instead of the original one.

So the solution is to
   1) Make the hidden worksheet visible
   2) Activate the original workbook
   3) Hide the worksheet
   4) Activate the new workbook with the copied sheet

Below's the code, hopefully can help someone who's facing the same problem out there.

Sub copyHidden()
  'Make hidden sheet visible for copying to new workbook
  Sheets("hiddenSheet").Visible = True
  Sheets("hiddenSheet").Copy

  'activate the original workbook to hide the sheet again
  newbook = Workbooks.Count
  Workbooks.Item(newbook - 1).Activate
  Worksheets("hiddenSheet").Visible = False

  'activate the new workbook with the copied sheet
  Workbooks(newbook).Activate
  ActiveSheet.Name = "copiedSheet"

  'the rest of your code here

End Sub

No comments:

Post a Comment