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.
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.
VBA Code:
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