0

enter image description here

How can I get these two Subs to run one after the other automatically without getting the "Select method of range class failed" error? I've done it before with multiple Subs that are in the same module by putting Sub Names at the end of previous Subs or by creating a new module and calling them all. However, in this case, none of these methods work. These are the two Subs in the same module:

Sub CopiarCeldas009()

Dim wbDestino As Workbook, _
    wsOrigen As Excel.Worksheet, _
    wsDestino As Excel.Worksheet, _
    rngOrigen As Excel.Range, _
    rngDestino As Excel.Range
    
Set wbDestino = Workbooks.Open(ActiveWorkbook.Path & "\009 CHRISTIANA.xlsx")

ThisWorkbook.Activate

Set wsOrigen = Worksheets("009 CHRISTIANA")
Set wsDestino = wbDestino.Worksheets("Nav Bank Book")

Const celdaOrigen = "A4"
Const celdaDestino = "A3"

Set rngOrigen = wsOrigen.Range(celdaOrigen)
Set rngDestino = wsDestino.Range(celdaDestino)

rngOrigen.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

rngDestino.PasteSpecial xlPasteValues
Application.CutCopyMode = False

wbDestino.Save
wbDestino.Close

End Sub

**Sub CopiarCeldas013()**

Dim wbDestino As Workbook, _
    wsOrigen As Excel.Worksheet, _
    wsDestino As Excel.Worksheet, _
    rngOrigen As Excel.Range, _
    rngDestino As Excel.Range
    
Set wbDestino = Workbooks.Open(ActiveWorkbook.Path & "\013 SARASOTA.xlsx")

ThisWorkbook.Activate

Set wsOrigen = Worksheets("013 SARASOTA")
Set wsDestino = wbDestino.Worksheets("Nav Bank Book")

Const celdaOrigen = "A4"
Const celdaDestino = "A3"

Set rngOrigen = wsOrigen.Range(celdaOrigen)
Set rngDestino = wsDestino.Range(celdaDestino)

rngOrigen.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

rngDestino.PasteSpecial xlPasteValues
Application.CutCopyMode = False

wbDestino.Save
wbDestino.Close

End Sub

Thank you,

braX
  • 11,506
  • 5
  • 20
  • 33
Julian A O'vega
  • 1
  • 1
  • Please show us your effort so far, not the 2 subs independant. and tell teh line where you get the error. – Aldert Aug 20 '22 at 15:25
  • 1
    You can't select a range from a sheet you are not on. You should not have to select the range, but use the range variable. – Davesexcel Aug 20 '22 at 15:54
  • 1
    Read [How to avoid select](https://stackoverflow.com/q/10714251/16578424) - you will learn a lot and be able to improve both routines. I am pretty sure they will work together afterwards. – Ike Aug 20 '22 at 15:55

0 Answers0