Copy and Paste Macro help

I'm not "expert" enough to write the code for you, but it seems to me that when you open the new workbook, it has the next sequential name (ie- Book2, Book3, Book4, etc) and therefore you get the error since the macro is looking for Book1 when it doesn't exist.

I would try to have the macro open the new workbook for you (rather than you doing it manually) and then (if the Macro still uses the Book# name) use the "ActiveWorkbook" command to paste to the new workbook.

===================

One other point to add to the above from S.N. As soon as you have created the new file do a save as operation and then always reference the new file name that you have created. This way as you switch between different books you can still get to the one you want.

Hope it Helps.
Sean.

Re: Copy and paste Macro - Thanks for suggestions and still trying to get an answer

Thanks for the two people who have given the following suggestions to my Excel question.
Actually when I open the new workbook, if it is Book2or Book3 etc., I rename it as Book1 and still the error message comes. I also tried closing Excel program and starting again. Book1 shows up but the same error message comes. I haven't tried the other suggestion of asking Excel to open a workbook for me and run the Macro.
I am sure there must be a way of writing the correct code for copying a part of the data from one file and writing it to another file and I am also sure that somebody would be able to help me.

Re: Copy and paste Macro - Thanks for suggestions and still trying to get an answer

Actually when I open the new workbook, if it is Book2or Book3 etc., I rename it as Book1 and still the error message comes. I also tried closing Excel program and starting again. Book1 shows up but the same error message comes. I haven't tried the other suggestion of asking Excel to open a workbook for me and run the Macro. I am sure there must be a way of writing the correct code for copying a part of the data from one file and writing it to another file and I am also sure that somebody would be able to help me.

Hi Pankaja

You need to omit the ".xls". If you will only have the two workbooks open you can use this. You need to run it from the Workbook you want to copy from.

Sub CopyBetween()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim wBook1 As String
Dim wBook2 As String
Dim sReply1 As Integer, sReply2 As String

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next
wBook1 = ActiveWorkbook.Name
ActiveWindow.ActivateNext
wBook2 = ActiveWorkbook.Name

If wBook2 = wBook1 Then
sReply1 = MsgBox("No other workbooks are open! Do you want to open one?", vbYesNo)
If sReply1 = vbNo Then Exit Sub
sReply2 = Application.Dialogs(xlDialogFindFile).Show
If sReply2 = "False" Then Exit Sub
End If

On Error GoTo 0

Windows(wBook1).Activate
Sheets("Sheet1").Range("A1:A10").Copy

Windows(wBook2).Activate
Sheets("Sheet2").Range("A7").PasteSpecial
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Dave

OzGrid Business Applications

Re: Copy and paste Macro - THANK YOU very much, Dave, for writing the code to "Copy/Paste"

Thank very much, SN, Sean and Dave for giving me suggestions, writing the codes for my macro "Copy/Paste". I appreciate your taking the time to help me.

Pankaja

for the two people who have given the following suggestions to my Excel question. : Actually when I open the new workbook, if it is Book2or Book3 etc., I rename it as Book1 and still the error message comes. I also tried closing Excel program and starting again. Book1 shows up but the same error message comes. I haven't tried the other suggestion of asking Excel to open a workbook for me and run the Macro. : I am sure there must be a way of writing the correct code for copying a part of the data from one file and writing it to another file and I am also sure that somebody would be able to help me. :

Hi Pankaja You need to omit the ".xls". If you will only have the two workbooks open you can use this. You need to run it from the Workbook you want to copy from.

Sub CopyBetween() 'Written by OzGrid Business Applications 'www.ozgrid.com Dim wBook1 As String Dim wBook2 As String Dim sReply1 As Integer, sReply2 As String Application.ScreenUpdating = False Application.EnableEvents = False On Error Resume Next wBook1 = ActiveWorkbook.Name ActiveWindow.ActivateNext wBook2 = ActiveWorkbook.Name If wBook2 = wBook1 Then sReply1 = MsgBox("No other workbooks are open! Do you want to open one?", vbYesNo) If sReply1 = vbNo Then Exit Sub sReply2 = Application.Dialogs(xlDialogFindFile).Show If sReply2 = "False" Then Exit Sub End If On Error GoTo 0 Windows(wBook1).Activate Sheets("Sheet1").Range("A1:A10").Copy Windows(wBook2).Activate Sheets("Sheet2").Range("A7").PasteSpecial Application.CutCopyMode = False ActiveWorkbook.Save ActiveWorkbook.Close Application.ScreenUpdating = True Application.EnableEvents = True End Sub Dave