Avatar of Jenedge73
Jenedge73
Flag for Afghanistan asked on

Error 438"Object Doesn't Support This Property or Method

I need an expert

I have a code to Copy one range to another range i a workbook
I keep getting the 438  at this point

Open in new window

Dest.Worksheet("Options").Activate
    Range("A57").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window

I'm using Excel 2010

Open in new window

Sub LoopFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim strExt As String
Dim strPath As String    ' Change folder name to the folder with the files in it
Dim strFilename As String

    strExt = "*.xls*"

    strPath = "C:\Users\rascott\Desktop\West\Prairie\Cat Code 7\"

    strFilename = Dir(strPath & strExt)

    While Len(strFilename) > 0

        Set wb = Workbooks.Open(Filename:=strPath & strFilename)


            Dim Source As Workbook
            Dim Dest As Workbook

    Set Dest = ThisWorkbook
   
    Set Source = Workbooks.Open("C:\Users\rascott\Desktop\Copy of Trade Class Working Copy - Sept 24.xlsx") 'change path

    Source.Worksheets("Template PRA").Range("A57:C337").Copy 'change sheet ref

    Dest.Worksheet("Options").Activate
    Range("A57").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

    Dest.Close False

    ThisWorkbook.Activate

    MsgBox "Done"
   
        wb.Close SaveChanges:=False

        strFilename = Dir
    Wend

End Sub

Open in new window

Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Avatar of undefined
Last Comment
Jenedge73

8/22/2022 - Mon
nutsch

Try changing

    Source.Worksheets("Template PRA").Range("A57:C337").Copy 'change sheet ref

    Dest.Worksheet("Options").Activate
    Range("A57").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window


to

 
   Source.Worksheets("Template PRA").Range("A57:C337").Copy 'change sheet ref

    Dest.Worksheet("Options").Range("A57").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window


Thomas
Jenedge73

ASKER
Now i get it here
Dest.Worksheet("Options").Range("A57").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window

Jenedge73

ASKER
I’m not very good at vba.  Could the problem be that i have to somehow set the destination workbook up differently.  

Open in new window

Set Dest = ThisWorkbook

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
nutsch

Are you runnin the code from your personal macro workbook? If yes, you should use activeworkbook
Jenedge73

ASKER
I'm runing it out of a open workbook.  I can't use the open activeworkbook because its in the folder and wouldn't it be closed in the loop?
Unless I'm missing something
ASKER CERTIFIED SOLUTION
nutsch

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jenedge73

ASKER
Thanks that was it.

Is there a way i can add a prompt to ask what source to use and range?
and what cell to paste too.

Open in new window

Sub Loop_Original()
Dim wb As Workbook
Dim ws As Worksheet
Dim strExt As String
Dim strPath As String    ' Change folder name to the folder with the files in it
Dim strFileName As String
Dim Source As Workbook
   
    'Source Workbook change to whatever
   
    Set Source = Workbooks.Open("C:\Users\rascott\Desktop\Copy of Trade Class Working Copy - Sept 24.xlsx") 'change path
   
    'Source Workbook above
   
    strExt = "*.xls*"
   
    strPath = "C:\Users\rascott\Desktop\West\Admin\Cat Code 6\"

    strFileName = Dir(strPath & strExt)

    While Len(strFileName) > 0

        Set wb = Workbooks.Open(Filename:=strPath & strFileName)
           
            Set VBAwb = ThisWorkbook
           
   'Choose which sheet to copy from
   
    Source.Worksheets("Template PRA").Range("A57:C337").Copy 'change sheet ref

   'Source Worksheet above
   
    Sheets("Options").Select
        Range("A57").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Application.CutCopyMode = False

    wb.Close SaveChanges:=True
   
    ThisWorkbook.Activate

    MsgBox "Done"

        strFileName = Dir
    Wend

End Sub

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.