Copying cells across workbooks doesn't work like it used to

Question

I've used the same subroutine in previous macros and it worked fine but now it simply won't work.

Public inputFile, outputFile as Worksheet

sub copy()

    Set inputFile = excel.Workbooks.Open(get_file(".xlsb", fullpath:=True))
    Set outputFile = Workbooks("Sales_Report.xlsm")


    inputFile.Sheets("Planilha1").Activate 'error: Method or data member not found (highlights ".Sheets")
    inputFile.Sheets("Planilha1").Cells.Select
    inputFile.Sheets("Planilha1").Cells.Copy

    outputFile.Sheets("Vales").Activate  
    outputFile.Sheets("Vales").Select.Cells
    Selection.Clear
    outputFile.Sheets("Vales").Range("A3").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

end sub

As soon as I run the macro the error diplayed is: Method or data member not found and then it highlights the Sheets method. I tried changing it to inputfile.Worksheets("Vales") and make the variables not public but still didn't work.

Please [edit] your post to include the **exact, complete error message** in your post. Also indicate which specific line it is, because you have three places where it refers to `inputFile.Sheets`, so saying *it highlights the `Sheets` method isn't helpful. Also see [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/62576) — Ken White, Aug 22 '19 at 21:22
You can just use `Workbooks.Open` in your code. You need to check if your `inputFile` was actually found, and thus opened, before proceeding. That depends on whatever is in `get_file` really. You could try `If intputFile is Nothing Then Exit Sub` — urdearboy, Aug 22 '19 at 21:39
What is `outputFile.Sheets("Vales").Select.Cells`? Did you swap the `Select` and `Cells` in the question by mistake? — BigBen, Aug 23 '19 at 02:32

urdearboy · Answer 1 · 2019-08-22T21:29:39.487

Public inputFile, outputFile as Worksheet          '<-- WORKSHEET

Set outputFile = Workbooks("Sales_Report.xlsm")    '<-- WORKBOOK

You have mismatched variable types here. Notice OutputFile is declared as a Worksheet and inside your macro you assigned this variable to a Workbook

Since this is a worksheet variable, objects such as Sheets are not available hence your error.

Side Note

Your public variable decliration is probably not working the way you think it is. Variable types need to be assigned one by one (sigh). In your statement you actually declared variables like so:

inputFile = Variant
outputFile = Worksheet

but I expect that you actually want

Public inputFile as Workbook, outputFile as Workbook

Copying cells across workbooks doesn't work like it used to

1 Answers1

Side Note