Control variable already in use - simple VBA script

Question

Getting control variable in use for this, I guess because of "ws", can someone help how to fix? Thanks

Sub CreateNewWBS()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim strFilename As String
    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        For Each ws In wbNew.Worksheets
            ws.Cells.Copy
            ws.Cells.PasteSpecial xlPasteValues

        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

Dim ws2 as Worksheet and use that instead? Also you're missing a Next. — BigBen
– BigBen, Commented Jul 29, 2020 at 16:18
I do not understand the purpose of wbThis? Surely, it is just ThisWorkbook, which will not (and literally can not) change during the macro execution? — Chronocidal
– Chronocidal, Commented Jul 29, 2020 at 16:30
@Chronocidal I guess just to differentiate between wbNew. The code is working without the Copy Paste Values portion. — poppyaf
– poppyaf, Commented Jul 29, 2020 at 16:39
@BigBen Can I get guidance how to "Set" wb2 before the Copy Paste Value portion? — poppyaf
– poppyaf, Commented Jul 29, 2020 at 16:40
I proposed ws2 and you don't Set anything. For Each ws2 in wbNew.Worksheets, ws2.Cells.Copy, ws2.Cells.PasteSpecial xlPasteValues. — BigBen
– BigBen, Commented Jul 29, 2020 at 16:49

poppyaf · Accepted Answer · 2020-07-29 17:21:09Z

2

Used ws2 and fixed Next statements, works now:

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim strFilename As String
Set wbThis = ThisWorkbook
For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name
    ws.Copy
    Set wbNew = ActiveWorkbook
        For Each ws2 In wbNew.Worksheets
            ws2.Cells.Copy
            ws2.Cells.PasteSpecial xlPasteValues
        Next
    wbNew.SaveAs strFilename
    wbNew.Close
Next ws
End Sub

answered Jul 29, 2020 at 17:21

poppyaf

313 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chronocidal · Accepted Answer · 2020-07-29 18:59:43Z

1

Here is a simplified version of your code:

Sub CreateNewWBs()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        With ActiveWorkbook
            .Worksheets(1).UsedRange.Value = .Worksheets(1).UsedRange.Value
            .SaveAs ThisWorkbook.Path & "/" & ws.Name
            .Close
        End With
    Next ws
End Sub

I have removed the second For loop, which was causing you issues (both because you had For without Next, and because you were trying to reuse an existing variable) on the basis that wbNew only ever contained 1 worksheet, replaced wbThis with ThisWorksheet, and swapped the .Copy:.PasteSpecial with .Value=.Value to avoid needing the clipboard.

answered Jul 29, 2020 at 18:59

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Collectives™ on Stack Overflow

Control variable already in use - simple VBA script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related