VBA thinks Integer is Object

Question

This code return Error 91 for line set value of the NotCopySheet variable. When I only keep the code to the MsgBox line, VBA run fine. I have a named range "NotCopy" in the sheet I run. I don't have much VBA experience. This is a code I found to do what I need. I will use the NotCopySheet variable to stop copying/delete a few first sheets.

Sub CopyWorkbookValue()

Dim Output As Workbook, Source As Workbook
Dim sh As Worksheet
Dim FileName As String
Dim OriginalName As String
Dim firstCell
Dim NotCopySheet As Integer

OriginalName = ActiveWorkbook.Name
NotCopySheet = ActiveSheet.Range("NotCopy").Cells(1, 1).Value
MsgBox "The number of sheet ignore is " & NotCopySheet

Application.ScreenUpdating = False
Set Source = ActiveWorkbook
Set Output = Workbooks.Add
Application.DisplayAlerts = False

Dim i As Integer

For Each sh In Source.Worksheets

    Dim newSheet As Worksheet

    ' select all used cells in the source sheet:
    sh.Activate
    sh.UsedRange.Select
    Application.CutCopyMode = False
    Selection.Copy

    ' create new destination sheet:
    Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count))
    newSheet.Name = sh.Name

    ' make sure the destination sheet is selected with the right cell:
    newSheet.Activate
    firstCell = sh.UsedRange.Cells(1, 1).Address
    newSheet.Range(firstCell).Select

    ' paste the values:
    Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths
    Range(firstCell).PasteSpecial Paste:=xlPasteFormats
    Range(firstCell).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=True, Transpose:=False

Next

' delete the sheets that were originally there
While Output.Sheets.Count > Source.Worksheets.Count
  Output.Sheets(1).Delete
Wend

FileName = "C:\Dropbox\0 EPAS Export\ValueOnly_" & OriginalName
Output.SaveAs FileName
'Output.Close
Application.ScreenUpdating = True

End Sub

¿You get Error 91 on line NotCopySheet = ActiveSheet.Range("NotCopy").Cells(1, 1).Value? What is the value of ActiveSheet.Range("NotCopy").Cells(1, 1).Value? Are you sure the range is names NotCopy? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Sep 13, 2018 at 9:27
I get errors where you try to name a sheet as an existing sheet name and also with Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths If the range is named correctly I get no errors with the aforementioned line. — QHarr
– QHarr, Commented Sep 13, 2018 at 9:28
@Foxfire And Burns And Burns: 1) Yes, that the line. 2) The value is 2 (=SHEET()). 3) I checked for the named range many times. — Tam Le
– Tam Le, Commented Sep 13, 2018 at 9:33
If the value is 2 (=SHEET()). 3), that's a text, and you are trying to store it in a numeric variable. That may cause the error. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Sep 13, 2018 at 9:36
The function in that cell is =SHEET(). The value that the function return is 2. The number displayed in the cell/range is 2. The range NotCopy has only 1 cell. — Tam Le
– Tam Le, Commented Sep 13, 2018 at 9:51

GMalc · Accepted Answer · 2018-09-14 13:38:09Z

0

After looking at your code, it can be accomplished in one line of code.

ActiveWorkbook.SaveCopyAs "C:\Dropbox\0 EPAS Export\ValueOnly_" & ThisWorkbook.Name & ".xlsx"

or better

ThisWorkbook.SaveCopyAs "C:\Dropbox\0 EPAS Export\ValueOnly_" & ThisWorkbook.Name & ".xlsx"

Update: ThisWorkbook.Name refers to the workbook that has the macro. From what i understood from your code you were trying to copy all the worksheets, from the ActiveWorkbook. Using ActiveWorkbook could have undesirable results, if another workbook is active when you run your macro. The ValueOne & workbook.name is the new workbook naming convention you were going to use. You also commented earlier that you wanted to remove a worksheet, you can accomplish that by adding code to your macro or just opening the new workbook and deleting the worksheet you want to get ride of.

edited Sep 14, 2018 at 13:38

answered Sep 13, 2018 at 17:38

GMalc

2,6381 gold badge11 silver badges16 bronze badges

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

4 Comments

Tam Le Over a year ago

I put the code in an add-in because I want to use this functionality excel-wise not just for a specific file. ThisWorkBook.Name would refer to the new workbook created as destination of copying.

Tam Le Over a year ago

I removed the sheets that not needed copying simply by not copying it. I just put this before the copying code. If sh.Index <= NotCopySheet Then Else

GMalc Over a year ago

@TamLe I figured you would either ask how to accomplish that or find the answer yourself, good job.

Tam Le Over a year ago

Thank you. I tried. That doesn't change the fact that I am still a very beginner at VBA. Still have a lot to ask.

Tam Le · Accepted Answer · 2018-09-14 17:54:55Z

0

Since this code is run from an add-in, I think the ActiveSheet may have difficulty implementing. So I changed the code slightly to make sure that the code would refer the operating workbook. Now it works fine.

Dim r As Range
Set r = ActiveWorkbook.Names("NotCopy").RefersToRange
NotCopySheet = r.Cells(1, 1).Value

answered Sep 14, 2018 at 17:54

Tam Le

3784 silver badges20 bronze badges

Comments

Frank Ball · Accepted Answer · 2018-09-14 18:37:19Z

0

The error is most likely being caused by the "ActiveSheet" in NotCopySheet = ActiveSheet.Range("NotCopy").Cells(1, 1).Value. If the ActiveSheet is not the sheet which has the named range "NotCopy", it will cause problems. Having said that, your code is far more complicated than it needs to be. All you're trying to do is convert all the formulas to values and then save the file under a new name.

   Sub CopyWorkbookValue()
    Dim NotCopySheet As Integer
    On Error GoTo ErrorExit
        NotCopySheet = Range("NotCopy").Cells(1, 1).Value
        MsgBox "The number of sheet ignore is " & NotCopySheet
       For Each sh In ThisWorkbook.Sheets
            sh.UsedRange.Copy
            sh.UsedRange.PasteSpecial Paste:=xlPasteValues
       Next sh
       ThisWorkbook.SaveCopyAs "C:\Dropbox\0 EPAS Export\ValueOnly_" & ThisWorkbook.Name & ".xlsx"
     Exit Sub
ErrorExit:
     MsgBox "Error text here."
    End Sub

Looking at your code, it looks like you're over-relying on the macro recorder to generate your code. While the macro recorder is useful, it also generates lot of unneeded code. Stop and think about what exactly it that you want to do and then use the macro recorder to generate the code snippets that will do what you want. Look at the code the macro recorder generates and figure what exactly each line is really doing and if you really need to include it in your code.

edited Sep 14, 2018 at 18:37

answered Sep 14, 2018 at 14:59

Frank Ball

1,1778 silver badges21 bronze badges

3 Comments

Frank Ball Over a year ago

Yes. Also I added some error handling to the code, so that if a user deletes the "NotCopy" named range, you can handle it and display the appropriate message.

Tam Le Over a year ago

How do I make it if there is no NotCopy range, the value of NotCopySheet become zero?

Frank Ball Over a year ago

stackoverflow.com/questions/12611900/…

Collectives™ on Stack Overflow

VBA thinks Integer is Object

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related