Run Time error 1004

vb2178
vb2178 used Ask the Experts™
on
I have a macro to copy date from one workbook to a date field in a different workbook. I am recieving error while selecting the cell to which the date is to be pasted. I have a command button to run this code.

Code is given below

Private Sub CommandButton2_Click()
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\ABC.xls"
        Windows("XYZ.xls").Activate
        Sheets("Calculation").Activate
        Range("C9").Select   ''''''********** (This is where I get error)
        Selection.Copy
        Windows("ABC.xls").Activate
        Sheets("Summary").Activate
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Private Sub CommandButton2_Click()
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\ABC.xls"
        Windows("XYZ.xls").Activate
        Sheets("Calculation").Activate
        Range("C9").Select   ''''''********** (This is where I get error)
        Selection.Copy
        Windows("ABC.xls").Activate
        Sheets("Summary").Activate
        Range("B3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
one of the better ways to do this would be like thisDim wbSource As Excel.WorkbookDim wbDest As Excel.WorkbookPrivate Sub CommandButton2_Click()            set wbDest = Workbooks.Open(Filename:="C:\Documents and Settings\ABC.xls")     set wbSource = Windows("XYZ.xls")     wbSource.Sheets("Calculation").Range("C9").Copy     wbDest.Sheets("Summary").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=FalseEnd SubLet me know

Private Sub CommandButton2_Click()
    dim wb1 as workbook
    dim wb2 as workbook
    set wb1 = workbooks("XYZ.xls")
    set wb2 = Workbooks.Open "C:\Documents and Settings\ABC.xls"
    wb1.Sheets("Calculation").Range("C9").copy         
    wb2.Sheets("Summary").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  
        :=False, Transpose:=False  
End Sub

Open in new window

Author

Commented:
I tried this. Run time error 13 is generated in line

set wbSource = Windows("XYZ.xls")
The command button is in XYZ.xls workbook -> Sheet calculate
change the first 2 lines of the code toset wbSource = ActiveWorkbookset wbDest = Workbooks.Open(Filename:="C:\Documents and Settings\ABC.xls")

Author

Commented:
Thanks Man..... Got it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial