activesheet.range.select error 438

Question

I need to copy data from a workbook to another, but anytime i tried to run my macro a get the runtime error "438" on the line containing the "select" operation. Thanks to all , and sorry if i've made noob errors but i'm relatively new to vba :)

here's my code:

Private Sub FileChoose_Click()
Dim fd As FileDialog
Dim FileName As String
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
Dim wb2 As Workbook 
Set fd = Application.FileDialog(msoFileDialogOpen) 
Dim filechosen As Integer
filechosen = fd.Show
fd.Title = "Scegliere il file da confrontare"
fd.InitialView = msoFileDialogViewSmallIcons
fd.Filters.Clear
fd.Filters.Add "Excel File", "*.xlsx"
fd.Filters.Add "Excel File con Macro", "*.xlsm"
fd.FilterIndex = 1
fd.ButtonName = "Apri"
If filechosen <> -1 Then
MsgBox "Nessun File Selezionato"
Else
FileName = fd.SelectedItems(1)
Set wb2 = Workbooks.Open(FileName)
Call Insert(wb2, wb1)
End If
End Sub


Private Sub Insert(wb2 As Workbook, wb1 As Workbook)
Dim myrange As Range
Set myrange = Range("B2:GT22")
For i = 1 To 24
wb2.Worksheets(i).Activate
wb2.ActiveSheet.myrange.Select <--- there is the error 
Selection.Copy 
wb1.Worksheets(i + 1).Activate
wb1.ActiveSheet.myrange.Select
Selection.Paste
Next i
End Sub

score 1 · Answer 1 · answered May 22 '15 at 10:31

1

The myrange variable is a range reference but you are using it like a string variable. You can actually do away with the Select method and use this:

Private Sub Insert(wb2 As Workbook, wb1 As Workbook)
    Dim myrange As String
    myrange = "B2:GT22"
    For i = 1 To 24
        wb2.Worksheets(i).Range(myrange).Copy 
        wb1.Worksheets(i + 1).Range(myrange).Paste
    Next i
End Sub

answered May 22 '15 at 10:31

ChipsLetten

2,923
1
11
28

Actually hes using it the right way, problem is with the location of MyRange its not in the "activesheet" – KKowalczyk May 22 '15 at 10:33

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

Another good reason why you should not use .Select

Is this what you are trying?

Private Sub Insert(wb2 As Workbook, wb1 As Workbook)
    Dim FromRng As Range, ToRng As Range
    Dim i As Long

    For i = 1 To 24
        Set FromRng = wb2.Worksheets(i).Range("B2:GT22")
        Set ToRng = wb1.Worksheets(i + 1).Range("B2:GT22")

        myrange.Copy ToRng
    Next i
End Sub

edited May 23 '17 at 11:43

Community

1
1

answered May 22 '15 at 10:39

Siddharth Rout

147,039
17
206
250

KKowalczyk · Answer 3 · 2015-05-22T10:32:01.883

0

take a look at place where you define MyRange before the error, first you define it then later on you activate another sheet and try to access MyRange while it is in previous sheet, that generates error, set MyRange after the :

 wb2.Worksheets(i).Activate

and it will work

edited May 22 '15 at 10:32

answered May 22 '15 at 10:26

KKowalczyk

333
2
7

activesheet.range.select error 438

3 Answers3