VBA for Hidden Worksheets

[helpexcel]

Hi,

How do you get a marco to work with hidden worksheets?

 

[gallen]

What issue are you having? A hidden sheet is still accessible from within VBA

 

[helpexcel]

I get a run-time error '1004':Select method of Worksheet class failed.

 

[gallen]

ahh yes, you can't select a hidden sheet. However it's rare you actually need to. But to do so you would have to make it visible, perform your code then hide it again.

If you post your code along with what you are trying to achieve maybe we can sort it.

 

[helpexcel]

I do have a bunch of "selects" in my code. Thanks for the help.

Here's an example:

Set ws3 = ThisWorkbook.Worksheets("Sheet3")
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Set ws4 = ThisWorkbook.Worksheets("Sheet4")
 
Dim lastrow, lastrow2, LastrowF, LastrowC As Long
lastrow = ws4.Cells(ws4.Rows.Count, "M").End(xlUp).Row
LastrowD = ws3.Cells(ws3.Rows.Count, "M").End(xlUp).Row + 5
lastrow2 = ws3.Cells(ws3.Rows.Count, "M").End(xlUp).Row
 
Dim FilterRange, PasteRange As Range
Set FilterRange = ws3.Range("M2:M" & lastrow2)
Set PasteRange = ws3.Range("M" & LastrowD)
 
    FilterRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=FilterRange, CopyToRange:=PasteRange, Unique:=True
    Selection.End(xlDown).Select
    ws3.Select
    Range("M" & LastrowD).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    ws4.Select
    ws4.Range("E3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
ws3.Select
    Range("M" & LastrowD).Select
        Range(Selection, Selection.End(xlDown)).ClearContents
 
Dim LastRowE As Long
LastRowE = ws4.Cells(wsW.Rows.Count, "E").End(xlUp).Row
  
    Dim lColumn As Long
    lColumn = ws2.Cells(2, wsC.Columns.Count).End(xlToLeft).Column + 1
   
    ws4.Range("E4:E" & LastRowE).Copy
    ws2.Cells(2, lColumn).PasteSpecial xlPasteValues, Transpose:=True
   
Application.CutCopyMode = False

ws2.Select
Columns("L:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("T:V").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
LastrowC = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
 
Range("L2") = "XXXX"
Range("L3:L" & LastrowC).Formula = "month(k3)”
Columns("L:L").Select
    Selection.NumberFormat = "0"

 

[gallen]

Firstly, Yes you do! Using selection is something all of us who started to learn by recording macros have done and regretted. I'll just give ou an example as re-writing your code would take a lot of time.

I would take the time to comment every line and explain to yourself what each line is trying to achieve. That way you can see errors in your logic more clearly. Once I started doing this, although it's more time to write, it saves much more time in re-writing broken code.

lets look at at a snippet and break each line down, We'll look at this part (I've numbered each line via a comment to easy reference)

    Selection.End(xlDown).Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    ws3.Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    Range("M" & LastrowD).Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 
    Range(Selection, Selection.End(xlDown)).Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL] 
    Selection.Cut '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5]#5[/URL] 
    ws4.Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6]#6[/URL] 
    ws4.Range("E3").Select '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7]#7[/URL] 
    ActiveSheet.Paste '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8]#8[/URL] 
    Application.CutCopyMode = False '[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9]#9[/URL]

I can see that ultimately you are cutting from ws3 and pasting to ws4. Cut and paste can be done in a single line of code.

Line #1 pointless as line #2 selects something else!

line #2 = select worksheet
line #3 = get last used row you wish cut
line #4 = select from the last row up to the first row to be cut
line # 5 = cut everything you've selected.
lin #6 = select ws4
line #7 = select E3
line #8 = paste cells cut from line #5
line #9 = deselect everything

In short you are using 9 lines of code to cut & paste what you need. I get confused as to what exactly you're cutting. It seems like everything from the last used row DOWN. There shouldn't be anything below the last used row. I tested your code and it selects everything form last used row to row number 1048576

Can you confirm what you are trying to achieve in plain English? so we can set about replacing all the selects?

As a guide. If I wanted to CUT all data from Cell M2 to the last used row of ws3 and paste it onto E3 of ws4 it would look like this (it would work on a hidden sheet too)

ws3.Range("M2:M" & LastrowD).Cut ws4.Range("E3")

No selecting needed.

 

[helpexcel]

Thank You so much!! I've gotten used to using code from this forum and altering it slightly, without really understanding it. For this code, I have a code that runs an advanced filter and puts the unique items in the same column, but 5 rows down. This code the copies and pastes those items to the other worksheet.

 

[helpexcel]

Thank You for the the help. Here is the shortened version of the top part of the code.

Set wsF = ThisWorkbook.Worksheets("Sheet1")
Set wsC = ThisWorkbook.Worksheets("Sheet2")

Dim lastrowD, lastrow2 As Long
LastrowD = ws1.Cells(ws1.Rows.Count, "L").End(xlUp).Row + 5
lastrow2 = ws1.Cells(ws1.Rows.Count, "L").End(xlUp).Row

Dim FilterRange, PasteRange As Range
Set FilterRange = ws1.Range("L2:L" & lastrow2)
Set PasteRange = ws1.Range("L" & LastrowD)

Dim FData As Range
Set FData = ws1.Range("L" & LastrowD)

FilterRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=FilterRange, CopyToRange:=PasteRange, Unique:=True

FData.CurrentRegion.Cut ws2.Range("L2")
ws1.Range("A2:R2").Copy ws2.Range("A2")

Application.CutCopyMode = False

Cells.Select
Cells.EntireColumn.AutoFit

Ws2.Cells.SpecialCells(xlCellTypeVisible)(1).Select