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

--
hachirok
-----------------------------------------------------------------------
hachiroku's Profile: http://www.excelforum.com/member.php...fo&userid=3403
View this thread: http://www.excelforum.com/showthread.php?threadid=54205

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

Thanks a lot for your help, Ivan.


--
hachiroku
------------------------------------------------------------------------
hachiroku's Profile: http://www.excelforum.com/member.php...o&userid=34039
View this thread: http://www.excelforum.com/showthread...hreadid=542058