unhide sheet, copy data, hide sheet - screenupdate = false

[titus.hanke]

Hi everyone,

I am stuck a little with something I just can't find the solution to in this forum.
I would like to:

1.) unhide a worksheet ("HELPERSHEET" in an open (and saved) workbook
2.) select a range from A3 to A62
3.) Copy the data in this range
4.) Paste this data into a .bat file
5.) Hide the "HELPERSHEET" work sheet
6.) Run the batch file.

In all of this I would not like the user to see that a workbook is being unhidden, so they don't start fiddling around with it. (screenupdate = false?).

This is what I have so far:

Sub sop_screenshot()

Dim LastRow As Integer
    LastRow = ActiveSheet.Range("A62").End(xlUp).Row
        Range("A3", "A62" & LastRow).Select
        
    Selection.copy

Open ThisWorkbook.Path & "\sop_hotel_chceck.bat" For Output As #1
For NR = 1 To Selection.Rows.Count
For NC = 1 To Selection.Columns.Count
ExpData = Selection.Cells(NR, NC).Value
If IsNumeric(ExpData) Then ExpData = Val(ExpData)
If IsEmpty(Selection.Cells(NR, NC)) Then ExpData = ""
If NC <> NumCols Then
Print #1, ExpData
Else
Print #1, ExpData
End If
Next NC
Next NR
Close #1

End Sub

I am actually quite proud of what I got so far, and it works just perfect when run from the active work sheet, where my data used to reside prior to me wanting to hide it.

Any ideas? Do you need any further information?

Thanks a million,

Titus

[titus.hanke]

Hi, I saw that quite a few of you have viewed this post. Unfortunately nobody has responded yet.. Please let me know if there is a problem in understanding my request and I will be please to elaborate.

Thanks so much to you all!
Titus.

[titus.hanke]

Wow, this must either interest nobody or be really tough.. I have never received 0 responses in this forum ;-)

If you think you can help but have questions, please do contact me!
Thanks, Titus

[shg]

Maybe like this:

Sub sop_screenshot()
    Dim sFile   As String
    Dim iFF     As Integer
    Dim cell    As Range

    sFile = ThisWorkbook.Path & "\sop_hotel_chceck.bat"
    
    iFF = FreeFile
    Open sFile For Output As #iFF

    With Worksheets("HELPERSHEET")
        For Each cell In .Range("A3", .Range("A62").End(xlUp))
            Print #iFF, cell.Text
        Next cell
    End With
    Close #iFF
    
    Shell sFile
End Sub

[royUK]

The user cannot actually "start fiddling around" with the sheet whilst the macro is running