Dynamic range with hidden worksheet name

Question

I am trying to create an archive option for some manual inputs. I had that working, but when archive was hidden, then I experienced some issues.

Sub Archive()
Dim sDumpRange  As String
Dim sDumpSheet As String
sDumpSheet = "Active archive"
If Range("C6") <> Empty Then
    pRow = Sheets(sDumpSheet).Range("I1")
       If pRow > 1 Then
          Range("A2:E" & pRow).Delete Shift:=xlUp
       Else
       End If
    sDumpRange = "'" & Sheets("Active archive") & "'!" & "A" & Range("A5000").End(xlUp).Row + 1
   ' sDumpRange = "A" & Range("A5000").End(xlUp).Row + 1
    Sheets("call-outs completed").Range("a10:e109").Copy
    Worksheets(sDumpSheet).Range(sDumpRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("call-outs completed").Range("a10:a109").ClearContents
    Sheets("call-outs completed").Select
    Range("A11").Select
Else
    MsgBox "Data not archived. Please select your name first and try again."
End If
End Sub

There is one cell counting how many rows are with older date than 2 months and then that old data is being purged (delete section with pRow).

I was trying different things, and now sDumpRange was modified and it is not working, but when it was, it looked like that was calculating the range in the wrong, data input sheet. That was ten being copied to archive from the wrong cell (Active Archive A11). And when refreshed, then data was overwritten as Input sheet is also being cleared. The key seems to be within sDumpRange calculation now.

score 2 · Answer 1 · edited May 23 '17 at 12:13

2

but when archive was hidden, then I experienced some issues.

It is but obvious because it is no longer the ActiveSheet and hence I keep on harping about THIS

You need to fully qualify the range objects. For example, change

Range("A2:E" & pRow).Delete Shift:=xlUp

to

Sheets(sDumpSheet).Range("A2:E" & pRow).Delete Shift:=xlUp

else it will delete the rows from the wrong sheet.

Similarly, please make the changes every where and incorporate the suggestions given the link that I mentioned above.

edited May 23 '17 at 12:13

Community

1
1

answered Nov 07 '13 at 10:16

Siddharth Rout

147,039
17
206
250

1

@Jean-FrançoisCorbett: Oh I will :D – Siddharth Rout Nov 07 '13 at 10:21
This line is still causing me problem. Copied cells are copied to the range calculated based on the wrong sheet. I have to add there sDumpSheet as a correct reference somewhere. I removed the row above. sDumpRange = "A" & Range("A5000").End(xlUp).Row + 1 – user2964203 Nov 07 '13 at 11:28
`Range("A5000")` You didn't qualify it correctly. It will always refer to the activesheet. Please read my answer again. – Siddharth Rout Nov 07 '13 at 12:05
Ok, so how to activate that sheet if it is hidden and worksheets protected? I guess I would need to add to s.DumpRange somehow the worksheet details. I had previously there line with s.DumpSheet select, but stopped working when sheet was hidden and protected. Not sure how to change that line mentioned in my previous comment to get the range dumprange correct. – user2964203 Nov 07 '13 at 12:49
Why do you need to activate it? Did you not go through the link that I gave in my answer above? – Siddharth Rout Nov 07 '13 at 15:38
sDumpRange = "A" & Sheets(sDumpSheet).Range("A5000").End(xlUp).Row + 1 looks like sorted. – user2964203 Nov 07 '13 at 17:47

score 0 · Answer 2 · answered Nov 07 '13 at 10:12

Operations on hidden sheets are not always completed as expected. This is true for PasteSpecial for sure.

What you can do to overcome this is unhide these sheets for the duration of your script and hide them back again when your script is done.

At this at the beginning:

Sheets(sDumpSheet).Visible = xlSheetVisible

And this at the end:

Sheets(sDumpSheet).Visible = xlSheetHidden

Also know you can hide a sheet from being seen from the Excel by changing the value to xlSheetVeryHidden, that way you can only unhide it from VBA code or the VBA Window...

I could do that, but I would prefer not to blink with some other sheets during the process. Also, workbook will be protected so probably that will crash. — user2964203, Nov 07 '13 at 11:31

Dynamic range with hidden worksheet name

2 Answers2