0

I'm trying (and failing) to write a little macro that selects a range from one workbook to transfer to another.

I named a Workbook 1 "InputWB" and the other Workbook "OutputWB".

Why is the following code not working? Any ideas are more than welcome.

Dim InputWB, OutputWBAs Workbook

InputWB.Worksheets("Database").Range(Cells(1, 2), Cells(4, 34)).Copy

And then I will past it in another outputsheet, but for some reason the Workbook.Worksheet does not seem to work.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Festina
  • 7
  • 2
  • 3
    InputWB is being assigned as a variant not a workbook. You also need to tell it what those workbooks are. – Warcupine Jan 22 '20 at 15:53
  • 2
    You also have a typo that i assume is from posting on SO and not in your actual code but OutputWB has the ```As``` as part of the name. – Warcupine Jan 22 '20 at 15:54
  • 4
    Also see https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells: `Range(Cells..., Cells...)` is problematic. – BigBen Jan 22 '20 at 15:56
  • 1
    You renamed in sheet or in vba-ide? Only ide rename ([CodeName](https://rubberduckvba.wordpress.com/2019/12/19/code-name-sheet1/)) will work like that! – ComputerVersteher Jan 22 '20 at 16:00
  • 2
    As stated by @Warcupine, inputWB won't be assigned as a workbook, unless specified on its own, rather than along with another dim. [See this question for details](https://stackoverflow.com/questions/34035991/vba-variable-declaration-okay-on-two-lines-but-not-when-comma-separated-compile). This could very well be one of your problems if it is silently converted into a string when fed a workbook name. – Plutian Jan 22 '20 at 16:04
  • 3
    `Dim InputWB As Workbook, OutputWB As Workbook` declares two workbooks. Next step is to assign them to a `Workbook` reference using the `Set` keyword. Since `InputWB` is an implicit `Variant` in your code, the error you're getting is "object required". If it were `As Workbook`, then the error would be error 91 / object reference not set - you cannot use an object variable before it's assigned. – Mathieu Guindon Jan 22 '20 at 16:11
  • Just got aware, you referenced a workbook! Then you have to set the workbook object reference (`Set inputWB = Workbooks.Open(path/to/wb)`. A workbook has no range. Only worksheets have ranges! So you want to select a Worksheet on a workbook to select the range! At that point my link above is an eyes opener. – ComputerVersteher Jan 22 '20 at 17:00
  • @ComputerVersteher just noticed that's my article! thanks for linking! =) – Mathieu Guindon Jan 22 '20 at 17:24
  • @MathieuGuindon **NO**! Thank you for spreading your knowledge! the [RubberduckVBA-Blog](https://rubberduckvba.wordpress.com/) and the awesome Add-In (resolving Bangs to explicit member call iwill be very handy, when I moved my FE to x64 (on x86 it gets out of system resources (surprising, with only 10000 inspection results, 90% on implicit default meber caused by Bang;() are mandatory. – ComputerVersteher Jan 22 '20 at 17:38

0 Answers0