CODEWITHSUNDEEP
vbaexcel
user2859557
user2859557

Reputation: 1

Converting workbook VBA module into a personal.xlsb causes read-only problems

I got my macro working a couple days back. Being the newbie that I am I was coding in the workbook-level and now I made it into a personal macro. When I run the exactly same code in other workbook as personal macro, I run into some issues. 1st issues I managed to fix with some better but not good enough reformatting:

With ThisWorkbook
        For k = 1 To ThisWorkbook.Sheets.Count
            If .Sheets(k).Name = "reportdata" Then
                blnFound = True
                Exit For
            End If
        Next k
        If blnFound = False Then
            .Sheets.Add
            With ThisWorkbook
                .Name = "reportdata" 'this is where the problem is at the moment
            End With
        End If
    End With

    Set datasheet = ThisWorkbook.Worksheets(1)
    Set reportsheet = ActiveWorkbook.Worksheets("reportdata")
    reportsheet.Move After:=Worksheets(1)

What I want to create here is another sheet called reportdata. This sheet would not be created if it already exists.

I think my problem probably lies in the ThisWorkbook reference. My index in the for loop seems to be off so it might be working on something else than I intended. My intention is that the user of this macro has a workbook with data from another source that has a sheet with always the same name. So the use case is pretty static.

How do I make the references in this part of the code work on a "global" scale and not just workbook level?

Thank you in advance for any tips you can give me!

Upvotes: 0

Views: 66

Answers (3)

Tom
Tom

Reputation: 9898

You can achieve what you're trying to do by using Error handling instead of looping. Have a look at the following (You can also condense a few things whilst you're at it as well)

Dim reportsheet as worksheet, datasheet as worksheet

With ThisWorkbook
    ' You can test if the sheet exists by handling the error if it doesnt
    On Error Resume Next
    Set reportsheet = .Sheets("reportdata")
    On Error GoTo 0

    ' Test if worksheet exists and add if not
    If reportsheet Is Nothing Then
        Set reportsheet = .Sheets.Add(after:=.Sheets(1))
        reportsheet.Name = "reportdata"
    End If

    Set datasheet = .Worksheets(1)
End With

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4714

        With ThisWorkbook
            .Name = "reportdata" 'this is where the problem is at the moment
        End With

That block expands to ThisWorkbook.Name = "reportdata", but a workbook doesn't have a writable Name property - what you should be naming is the sheet you just added not the workbook.

Upvotes: 2

Raunak Thomas
Raunak Thomas

Reputation: 1403

It should be 

.Sheets(k).Name = "reportdata"

Right?

Plus for your info ActiveWorkbook and ThisWorkbook are different. The former is the activated workbook while the latter is the workbook where the VBA code is written

Upvotes: 0

Related Questions