Hi All,
My VBA experience consists of recording the occasional macro and copy/pasting code from websites like this to achieve what I need. However, my current need requires an understanding I simply do not have. I'm hoping that one of you fine citizens will help me.
I work with an image analysis software that generates reports via Excel templates. Templates are customizable, with coded tags telling the application which data to put where. Order of generation is as follows:
Select template from within app > app opens/scans XLSM copy of template > data transfers according to presence of coded tags > prompt to name/save XLSM
> file written to Windows directory
I am using this tool to transfer a single column of numbers to Excel. The numbers come over in the order items were analyzed on the image, but I need them in ascending order. While I would manually sort, the following conditions complicate things:
1. The sheet containing the transferred data will be hidden (its cells are referenced for display on another sheet)
2. The sheet referencing the data cells will be locked from sorting
3. The workbook itself will be protected to prevent unhiding the data sheet
4. My network is set up so that I cannot modify files after being saved to the directory (for security)
Therefore, I need a macro to sort my one column of data located on a hidden sheet in ascending fashion, and I seek to have it apply upon the save operation of the reporting procedure above (in bold) via "Private Sub Workbook_AfterSave(ByVal Success As Boolean)".
Attached is a stripped down version of the template I'm using. "Data" (Sheet2) is where I'm aiming the transfer (Column C). "Report" (Sheet1) is the display page.
With the help of a colleague, I have put together a bit of code, but clearly it does not meet the conditions above, nor does it apply upon the save operation of the file as intended:
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
On Error Resume Next
If Not Sheet2.Visible Then
If Not Intersect(Range("C1"), Range("C:C")) Is Nothing Then
Range("C1").Sort key1:=Range("C2"), _
order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub
Any help you can provide is MUCH APPRECIATED!
Bookmarks