1

I have one workbook with many sheets we'll call "type A" and an equal amount of "type B" sheets in the same workbook that correspond to a given type A sheet.

For simplicity, lets say my sheets go: red, yellow, blue, dark red, dark yellow then dark blue. I want to copy both red sheets to a brand new workbook, then both yellows to a different new workbook, and so on. I also want the name of the new workbook file to be the name of the type A file (e.g. Red). Any help would be greatly appreciated.

Here's the VBA I have so far. I am able to copy the first combo to a new workbook (i.e. both reds) but after that I receive a "424" error. I am using an "i" loop and referencing sheets by their number for simplicity/generalization. 

Sub export2sheets()
Dim twb As Workbook
Set twb = ThisWorkbook
Dim i As Integer
XPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 1 To 2
    twb.Activate
    Worksheets(Array(i, (i + 2))).Copy
    With ActiveWorkbook
         Application.ActiveWorkbook.SaveAs Filename:=XPath & "\" & xWs.Name & ".xlsx"
         Application.ActiveWorkbook.Close False
    End With
    Workbooks.Add
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

EDIT 1: the code below works but it does not name the file or close it as intended, ostensibly because I removed the lines of code to do so.

Sub export2sheets()
Dim twb As Workbook
Set twb = ThisWorkbook
Dim i As Integer
XPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 1 To 2
    twb.Activate
    Worksheets(Array(i, (i + 3))).Copy
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

EDIT 2: Most recent code is below.

  • I have used n to generalize the number of files/type A sheets.
  • I have removed anything related to xPath.
  • I have changed xWs to Worksheets(i).
  • I have removed Application.ActiveWorkbook.
  • i have changed Close False to Close True because I want it to close the file when finished.

Any ideas why the code is giving me a "52" error code when pressing F8 at the SaveAs line? It currently executes up until SaveAs, thus it does not change the filename or close the file. Also, for some reason Save and Close False work, but if either SaveAs or Close True is used, it does not work.

Sub export2sheets()
    Dim twb As Workbook
    Set twb = ThisWorkbook
    Dim i As Integer
    Dim n As Integer
    n = 3 'set n = the number of type A files
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For i = 1 To n
        twb.Worksheets(Array(i, (i + n))).Copy
        SaveAs Filename:=Worksheets(i).Name & ".xlsx"
        Close True
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
hunter stevens
  • 45
  • 1
  • 7
  • What line do you receive your error on? When you say that the sheets are that color, do you mean that the tab color is red, a large portion of the cells are red, etc ? – Marcucciboy2 Sep 21 '18 at 13:08
  • Sorry, I'll clarify: When my cursor is on the **first** `Application.ActiveWorkbook...` line and I press F8, I receive the 424 error. And I just colored a couple cells in each worksheet for simplicity. – hunter stevens Sep 21 '18 at 13:14
  • Ah, 2 big things. You're relying too heavily on the `ActiveWorkbook`, you should instead make a `Workbook` variable to store the 'current' workbook you're working on – Marcucciboy2 Sep 21 '18 at 13:17
  • and 2, when you use the `With` statement you don't have to re-specify the previous properties. So, for example you can change `Application.ActiveWorkbook.SaveAs Filename:=XPath & "\" & xWs.Name & ".xlsx"` to `.SaveAs Filename:=XPath & "\" & xWs.Name & ".xlsx"` – Marcucciboy2 Sep 21 '18 at 13:19
  • take a look at this classic post to see why you really shouldn't use `ActiveWorkbook`, `Select`, or `Activate` to name a few unless you have no other choice https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Marcucciboy2 Sep 21 '18 at 13:21
  • I will try to implement your feedback and respond shortly. Additionally, please see my edit to my post above. I removed the lines intended to name and close the file, causing my code to run successfully (minus naming and closing the file). So as you pointed out, it appears the `ActiveWorkbook` part is the issue. – hunter stevens Sep 21 '18 at 13:21
  • Yeah, while trimming off the `Application.ActiveWorkbook` part of those two lines might make the original 'work' (excluding the color renaming), there are several parts about it that I couldn't recommend keeping :) – Marcucciboy2 Sep 21 '18 at 13:23
  • @Marcucciboy2 Thank you very much for your suggestions. I have made a second **EDIT** to my post above with new code implementing your feedback but I am gettting a "52" error at the `SaveAs` line. This seems like a simple fix but I'm having trouble figuring out what the issue is. – hunter stevens Sep 21 '18 at 15:38
  • you're making progress for sure! your current problem is that you need a second workbook variable that you can paste into and then save and close. – Marcucciboy2 Sep 21 '18 at 19:17
  • After a lot of reworking, I tried reworking my `SaveAs` line and successfully used this line instead `ActiveWorkbook.SaveAs Worksheets(i).Name`. Although `Close True` still isn't working, I wont mind just closing a bunch of documents since they're already saved. Thank you for your help. I'll mark this as resolved. – hunter stevens Sep 21 '18 at 19:24
  • Can't mark it as resolved without an actual answer :P If you wanted to delete and repost the question I wouldn't mind taking a crack at it, but the thing is that you'll have to split this into 2 questions - the first asking how you can copy a worksheet over, and the second asking how to pick them out by color – Marcucciboy2 Sep 21 '18 at 19:27
  • @Marcucciboy2 I am honestly not sure how I mark this as solved and give you credit since I am new to the site. – hunter stevens Sep 21 '18 at 19:27
  • Don't worry I only gave you a bit of advice, you deserve the credit. You can't mark it resolved unless I add an actual 'answer' instead of just commenting – Marcucciboy2 Sep 21 '18 at 19:30
  • All right cool I won't worry about it. Thanks! – hunter stevens Sep 21 '18 at 19:49

0 Answers0