Guest User

Untitled

a guest
Oct 23rd, 2017
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.95 KB | None | 0 0
  1. Sub Copy_Sheets()
  2. Dim strNames As String, strWSName As String
  3. Dim arrNames, CopyNames
  4. Dim wbAct As Workbook
  5. Dim i As Long
  6. Dim arrlist As Object
  7.  
  8. Set arrlist = CreateObject("system.collections.arraylist")
  9. arrlist.Add "1234"
  10. arrlist.Add "5678"
  11.  
  12.  
  13. Set wbAct = ActiveWorkbook
  14. For Each Item In arrlist
  15.  
  16. For i = 1 To Sheets.Count
  17. strNames = strNames & "," & Sheets(i).Name
  18. Next i
  19. arrNames = Split(Mid(strNames, 2), ",")
  20.  
  21. 'strWSName =("1234")
  22. strWSName = Item
  23.  
  24. Application.ScreenUpdating = False
  25. CopyNames = Filter(arrNames, strWSName, True, vbTextCompare)
  26. If UBound(CopyNames) > -1 Then
  27. Sheets(CopyNames).Copy
  28. ActiveWorkbook.SaveAs Filename:=strWSName & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  29. ActiveWorkbook.Close
  30. wbAct.Activate
  31. Else
  32. MsgBox "No sheets found: " & strWSName
  33. End If
  34.  
  35. Next Item
  36.  
  37. Application.ScreenUpdating = True
  38.  
  39. End Sub
Add Comment
Please, Sign In to add comment