macro returning error.

[jainvik]

Hi,
I have created a macro which first selects a portion of the activ
sheet and copies it then it opens a new workbook and starting with A
cell it pastes the selection. then it does some basic formatting lik
resizing rows and columns for the best view... but it is unable to cop
value from one perticular cell in the original workbook. that cel
produces results of some basic computations on the original sheet. no
what i did i recreated that macro which after completing all the abov
mentioned stuff goes back to the original sheet and copies the value
from that particular cell and pastes it on the already opened anothe
workbook **(( which is opened in the first part of this macro))** no
whenever i am running this macro it completes the first part and goe
back to the original sheet copies the value from that particular cel
and before pasting the value on the new workbook opened generates erro
"9". i am pasting the code as well...

Sub viki()
'
' viki Macro
' Macro recorded 3/15/2004 by Vikram2918
'
' Keyboard Shortcut: Ctrl+y
'
Range("A1:L84").Select
Range("B1").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:L").Select
Selection.ColumnWidth = 13.57
Selection.ColumnWidth = 11.43
Rows("1:84").Select
Selection.RowHeight = 21.75
ActiveWindow.SmallScroll Down:=-33
Selection.RowHeight = 28.5
ActiveWindow.SmallScroll Down:=-45
Range("G5").Select
Windows("DTS.vikram_jain.xls").Activate
Range("L5").Select
Application.CutCopyMode = False
Selection.Copy

Windows("Book4").Activate

Range("L5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub



Notice the command in red... every time i run this macro it creates
new workbook which has the name in continuation with the workbook
opened previousley.. BOOK1,BOOK2,BOOK3... and so on... now when
created this macro already 3 new workbooks were opened previously.. s
it has direction to activate "BOOK4" but when i will run this macr
next time in the initial stage it will open the new workbook by th
name BOOK 5 and after compleating first set of instructions it will tr
to activate BOOK4 which is not there as i have already saved and close
the book which was opened last time i ran this macro. Now th
Question... Is there any way we can change the name in the workboo
activation command dynamically or is there another and better approac
to it... PLEASE HELP.. I AM UNDER SEVERE WORK PRESSURE THESE DAYS AN
THIS MACRO CAN SAVE My JOB... LET ME KNOW IF THERE IS ANY OTHER DETAI
NEEDED

 

[kittenkj]

Instead of opening a new workbook to paste into, try opening a name
workbook....such as new Entry sheet.xls or something like that. Tha
way the macro will have no doubt which file to open an paste into.
When it has run you can always save the new file as a new name. Doe
this help

 

[Anders S]

Hi,

When recording the macro, save the new workbook immediately after it has been created. You will get something like:

---
Workbooks.Add
ChDir "C:\Documents and Settings\John\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\John\Desktop\NewBook.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
---

Then you will have no problem to address the new workbook.

HTH
Anders Silvén

 

[mudraker]

Try this

Sub viki()
'
' viki Macro
' Macro recorded 3/15/2004 by Vikram2918
' Modified by Mudraker
' Keyboard Shortcut: Ctrl+y
'
Dim ws1 As Worksheet
Dim wbNew As Workbook


Set ws1 = ActiveSheet
Set wbNew = Workbooks.Add

ws1.Range("A1:L84").Copy
wbNew.Sheets(1).Range("a1").Paste
Columns("A:L").Select
wbNew.Sheets(1).ColumnWidth = 11.43
wbNew.Sheets(1).RowHeight = 28.5
wbNew.Sheets(1).Range("l5").Value = ws1.Range("l5").Value
End Su

 

[jainvik]

Thanks a ton for your replies guys... I am falling short of words in
showing my gratitude... Anders S and mudraker your modules worked
great... but I ended up using the approach suggested by kittenkj... you
have really saved me... from now onwards I will be a frequent visitor
and try to help with whatever little knowledge I have... for your
information I just have functional knowledge of s/w. but i am a DELL
certified H/W technician.. anytime anybody needs assistence reguarding
any computer H/W compatibility driver installation help.. please fell
free to mail me at my ID.. Thanks a BUNCH once again...

mudraker
Anders S
kittenkj

Lots of Regards
Viki