Help troubleshooting missing columns

[tpthatsme]

Hello Excel experts,

I have a mystery. I "wrote" (more like copy/paste, trial/error) some VBA to modify an Excel file and then save one of its sheets as a CSV. In order to make things simple for the users, I have hidden some columns. It seems that when some users run the macro, the hidden columns do not copy over to the CSV. It is very strange, I will get a call that the CSV is wrong... and it will be. I will run the macro myself and the CSV will be correct. I would not have changed any data. Could a user's Excel be set up differently to not copy hidden columns? I cannot recreate the problem output.

Here is the code:
I now realize that my line Range("C:C,K:K,N:U").EntireColumn.Hidden = False needs to be moved below Worksheets("Routing Table").Activate but this doesn't seem to be the problem. The bad results are missing 10 columns... just like if C, K and N-U were deleted.

Private Sub Auto_Open()
    Worksheets("Instructions").Activate
    Worksheets("Instructions").Unprotect
    Worksheets("Instructions").Buttons("Button 1").Text = "Create Routing Table"
    Range("B2:K11").Interior.ColorIndex = 8
    Worksheets("Routing Table").Activate
    Columns("A:Z").EntireColumn.Hidden = False
    Range("C:C,K:K,N:U").EntireColumn.Hidden = True
    On Error Resume Next
    Worksheets("Routing Table").ShowAllData
    On Error GoTo 0
    Range("A1").Select
    Worksheets("Instructions").Activate
    Worksheets("Instructions").Protect
End Sub

Sub RTMaker()
    Worksheets("Instructions").Unprotect
    Worksheets("Instructions").Buttons("Button 1").Text = "Running..."
    Range("B2:K11").Interior.ColorIndex = 6
    Wait 1
    Application.ScreenUpdating = False
    Range("C:C,K:K,N:U").EntireColumn.Hidden = False

    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet
    Worksheets("Routing Table").Activate
    Range("H:H").NumberFormat = "00000"
    Range("G:G").NumberFormat = "0000"
    Range("F:F").NumberFormat = "000"
    Range("E:E").NumberFormat = "00"
    Range("D:D").NumberFormat = "0"
    
' Delete any content of protected columns
    Range("C2:C1048576").Select
    Selection.ClearContents
    Range("K2:K1048576").Select
    Selection.ClearContents
    Range("N2:U1048576").Select
    Selection.ClearContents
    
' Delete all the spaces
    Range("A2:M1048576").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Range("A1").Select
    
' Copies the Routing Table Maker data to the CSV file
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    ActiveWorkbook.SaveAs Filename:= _
    "http://sharepoint_site_here/file name.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Range("C:C,K:K,N:U").EntireColumn.Hidden = True
    Range("A1").Select

' Complete
    Worksheets("Instructions").Activate
    Application.ScreenUpdating = True
    Worksheets("Instructions").Buttons("Button 1").Text = "Complete"
    Range("B2:K11").Interior.ColorIndex = 4
    Range("C3").Select
    Range("H11") = "Last ran: " & Now()
    Worksheets("Instructions").Protect
    ActiveWorkbook.Save

End Sub
Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

 

[Yongle]

Perhaps sheet "Routing Table" is not active when users run RTMaker
That WOULD leave the columns hidden for the duration of the code

Move the line (as you mention) or use the same line as in Workbook_Open :

Unhide the columns below this line
Worksheets("Routing Table").Activate
  Columns("A:Z").EntireColumn.Hidden = False

Comment
VBA usually does not require sheets or ranges to be selected or activated
Relying on ranges without a sheet reference is risky
Placing a line in the WRONG place (as you have recognised) could result in a different sheet (to the one expected) being active when the code runs
Editing the code later is also tricky - it is essential to determine which objects are active - good code should make that obvious
Additionally EVERY selection slows the code down

Example
Your code ...

    Worksheets("Routing Table").Activate
    Range("H:H").NumberFormat = "00000"
    Range("G:G").NumberFormat = "0000"
    Range("F:F").NumberFormat = "000"
    Range("E:E").NumberFormat = "00"
    Range("D:D").NumberFormat = "0"

' Delete any content of protected columns
    Range("C2:C1048576").Select
    Selection.ClearContents
    Range("K2:K1048576").Select
    Selection.ClearContents
    Range("N2:U1048576").Select
    Selection.ClearContents

Without selecting or activating becomes ...

    With Worksheets("Routing Table")
        .Range("H:H").NumberFormat = "00000"
        .Range("G:G").NumberFormat = "0000"
        .Range("F:F").NumberFormat = "000"
        .Range("E:E").NumberFormat = "00"
        .Range("D:D").NumberFormat = "0"

' Delete any content of protected columns
        .Range("C2:C1048576").ClearContents
        .Range("K2:K1048576").ClearContents
        .Range("N2:U1048576").ClearContents
    End With

 

[tpthatsme]

Perhaps sheet "Routing Table" is not active when users run RTMaker
That WOULD leave the columns hidden for the duration of the code

Hi Yongle,
Thanks so much for the With tip. I bet that will help out a lot. As for the active sheet; I have the macro button on sheet "Instructions", so the user will be on the "Instructions" sheet first. Then the "Routing Table" sheet will become active and do a bunch of stuff. I think that what you are saying is that all this stuff can happen without having to physically activate the sheets... that it can be done behind the scenes with the With command. I will give that a go. Also... do you know... is it possible that the copy/paste function can work differently from pc to pc with regards to hidden columns? Like my settings of Excel copies all columns where someone else's only copies visible columns?

Thanks!

 

[Yongle]

Also... do you know... is it possible that the copy/paste function can work differently from pc to pc with regards to hidden columns? Like my settings of Excel copies all columns where someone else's only copies visible columns?

I would not expect that to happen
- I think the explanation is that the columns are not being unhidden by the code