Solved Select new Workbook and new Worksheet

June 29, 2012 at 05:38:36
Specs: Windows XP
I just cannot get this VBA code to do what it is suppose to do. The first command (Copy and Paste) is in 1 workbook and the second command in another Workbook with different Worksheets (Copy and Paste). Can somebody assist. Thanks


Private Sub CommandButton5_Click()
Range("e3").Copy
Range("e3").PasteSpecial xlPasteValuesAndNumberFormats
ChDir "C:\"
mypath = "c:\Small B developm\Biz Africa\Payment System\"
Workbooks("Cash flow - paym. in advance.xls").Activate
Worksheets("Input - DISBURSEMENT").Activate
Range("c21:c22").Select (This is where Run-time error 1004 is - Range class failed)
Selection.Copy

Range("c21:c22").PasteSpecial xlPasteValuesAndNumberFormats

End Sub


See More: Select new Workbook and new Worksheet

Report •


#1
June 29, 2012 at 08:03:03
✔ Best Answer
First, before posting code in this forum, please click on the blue Line at the bottom of this post and read the instructions found via that link.

Second, VBA code gets "confused" when you try to access a range after Activating a different workbook/worksheet from the one that the code resides in.

Try this:

ActiveSheet.Range("C21:C22").Select

However, rarely do you have Select (or even Activate) an object in order to perform a VBA action on it.

Things like:

   ActiveSheet.Range("C21:C22").Select 
    ActiveSheet.Selection.Copy 

can be written as single line:

   ActiveSheet.Range("C21:C22").Copy 

Finally, since it doesn't appear that your code Opens the "Cash flow - paym. in advance.xls" workbook, I'm not sure I understand why you use ChDir and mypath.

If the workbook is already open when you run this code, I think that this should work for you:

Private Sub CommandButton5_Click()
  Range("e3").Copy
  Range("e3").PasteSpecial xlPasteValuesAndNumberFormats
     With Workbooks("Cash flow - paym. in advance.xls"). _
          Worksheets("Input - DISBURSEMENT").Range("c21:c22")
       .Copy
       .PasteSpecial xlPasteValuesAndNumberFormats
     End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 30, 2012 at 01:00:21
Thanx DerbyDad03, I have applied the "bottom part" of your code and it is working

Report •

Related Solutions


Ask Question