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

MS Word VBA - Remove Cross Reference Links

Those who use MS Word extensively would likely have come across docs that use the cross-reference link feature.

And sometimes, due to the document passing through many hands, and when it finally comes to you, you find that the cross-reference links are all in a mess, sometimes with Error! messages here and there.

Someone came to find me the other day, and his question was - Could you help me remove the cross-reference links, just the links, but retain the words?

Ok, so I searched the web, and saw a seemingly nifty solution: Ctrl+A, and then Ctrl+Shift+F9

We happily used this, only to find out moments later that the references for all the Figure and Table Captions were all gone as well! This meant that we are no longer able to create a List of Tables/Figures, and that if more tables and figures are to be added, we have to MANUALLY update all the table and figure numbers. Imagine the nightmare with over 100 of them, each. That's a total of more than 200. Gosh.

In desperation, I sought a solution via VBA. After much trial and error, I managed to come up with this. May this help anyone who needs it.

Sub RemoveCrossRefLink()

  Dim objDoc As Document

  Dim objFld As Field

  Dim sFldStr As String

  Dim i As Long, lFldStart As Long

  Set objDoc = ActiveDocument

  ' Loop thru fields in the doc

  For Each objFld In objDoc.Fields

     If objFld.Type = wdFieldRef Then

        objFld.Select

        Selection.Fields.Unlink

        objFld.Update

     End If

  Next objFld

End Sub