-1

I've been working with some vba code to copy multiple worksheets from a variety of files into different workbooks. Whilst the code has worked well it does have some limitations. In particular when the base file contains hidden sheets.

Basically when the "base" file contains a hidden sheet, lets say Extract sheet 2 is hidden. The following code will pull an error at the line Sheets("sheet2").Select, when trying to copy sheet 2.

From my understanding this is because it initially recognises there is a sheet in the Instr code, even though it is hidden. Then down a few lines it is trying to then select the sheet, which it fails to do because it is hidden.

Any help would be much appreciated, I only want to run the Instr code on active not hidden sheets and ignore all hidden sheets. Example code as follows

Dim book1 as workbook
Dim book2 as workbook
Dim ws as worksheet
book1.Activate

For Each ws In ActiveWorkbook.Worksheets
If InStr(1, ws.Name, "sheet2") Then
book2.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "X2"
book1.Activate
Sheets("sheet2").Select
Set ws = ActiveSheet
If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Cells.Select
Application.CutCopyMode = False
Selection.Copy
book2.Activate
Sheets("x").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.Columns.AutoFit


End If
      Exit For

Next`
gtm1874
  • 39
  • 4
  • You should read [How to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|137.8127). – SJR Dec 03 '18 at 16:41
  • You should update your question with the additional requirement for sheet selection as it would appear being visibile is not the only criteria. – QHarr Dec 04 '18 at 17:55

1 Answers1

2

General example below of how to only execute code on visible sheets. You will need to prefix all objects with the ws identifier so it is clear what sheet the code should be referencing. For instance: ws.Range("A1").Copy

Dim ws as Worksheet

For Each ws in Worksheets
    If ws.Visible Then
        'Do what with visible sheets?
    End If
Next ws

Also, you should write out every instance of .Select from your code (including the other sub you are calling to make a selection). For instnace, this bit of code

Sheets("x").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Can be reduced to this (Notice you can skip the .Select and arrive right at the point. 

Sheets("x").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("x").Range("A1").PasteSpecial Paste:=xlPasteFormats
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Hi, many thanks for your answer. However I'm still have trouble running this code succesffully. Basically my problem is I do not want to do something to all visible sheets, so If ws.visible alone will not solve the problem. I'm looking for If ws.visible and If the worksheet name is like xyz Then execute this code. I've come up with the following example but it seems to miss sheets even although I know they are visible and the name is correct.....If Instr(1,ws.name, "Sheet2") AND ws.visible Then – gtm1874 Dec 04 '18 at 10:24