Runtime error 91 help

H

hachiroku

Today, I recorded a new macro to copy certain columns of one workbook t
a report template.

Because every month I will need to copy new data of the same forma
from a new workbook to the report template I tried using th
personal.xls to make that macro available to all workbooks

I tried to setup a variable for the workbook, so the code will work o
all workbooks. Here is what I have so far. I get a runtime error 91
object variable or with block variable not set.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/15/2006 by CHEUANDK
'
' Keyboard Shortcut: Ctrl+s
'
dim curBook as workbook

curBook = activeworkbook.name


Range("E3:E4000").Select
Selection.Copy
Windows("PERSONAL.XLS").Activate
Windows("Large Amount Report By ARM Templete.xls").Activate
Range("A10").Select
ActiveSheet.Paste

Workbooks(curBook).Activate
Range("D3:D4000").Select
Selection.Copy

Windows("Large Amount Report By ARM Templete.xls").Activate
Range("B10").Select
ActiveSheet.Paste

Workbooks(curBook).Activate
Range("H3:I4000").Select
Selection.Copy

Windows("Large Amount Report By ARM Templete.xls").Activate
Range("C10").Select
ActiveSheet.Paste


End Sub

Can someone adjust this code, your help is appreciated
 
I

Ivan Raiminius

Hi Hachiroku,

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/15/2006 by CHEUANDK
'
' Keyboard Shortcut: Ctrl+s
'
dim curBook as workbook

set curBook = activeworkbook

Range("E3:E4000").Select
Selection.Copy
Windows("PERSONAL.XLS").Activate
Windows("Large Amount Report By ARM Templete.xls").Activate
Range("A10").Select
ActiveSheet.Paste

curBook.Activate
Range("D3:D4000").Select
Selection.Copy

Windows("Large Amount Report By ARM Templete.xls").Activate
Range("B10").Select
ActiveSheet.Paste

curBook.Activate
Range("H3:I4000").Select
Selection.Copy

Windows("Large Amount Report By ARM Templete.xls").Activate
Range("C10").Select
ActiveSheet.Paste

End Sub

You dimensioned curBook as workbook (which is object) and tried to
assign it value activeworkbook.name (which is string) and didn't set
the object to the variable.

Regards,
Ivan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do until code attempt.. 2
Don't want to hard-code file name in macro 3
VBA Filter 2
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1
Help with activating window from Application.GetOpenFilename 4
Help making this macro dynamic :) 1
I need help with a "Run-Time Error 13: Type Mismatch" 2
Copy a table from one workbook to ALL worksheets in a different wb 1

Top