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
Range("A57").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
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\
strFilename = Dir(strPath & strExt)
While Len(strFilename) > 0
Set wb = Workbooks.Open(Filename:=s
Dim Source As Workbook
Dim Dest As Workbook
Set Dest = ThisWorkbook
Set Source = Workbooks.Open("C:\Users\r
Source.Worksheets("Templat
Dest.Worksheet("Options").
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
I have a code to Copy one range to another range i a workbook
I keep getting the 438 at this point
Range("A57").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
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\
strFilename = Dir(strPath & strExt)
While Len(strFilename) > 0
Set wb = Workbooks.Open(Filename:=s
Dim Source As Workbook
Dim Dest As Workbook
Set Dest = ThisWorkbook
Set Source = Workbooks.Open("C:\Users\r
Source.Worksheets("Templat
Dest.Worksheet("Options").
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
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office
Last Comment
Jenedge73
ASKER
Now i get it here
Dest.Worksheet("Options").Range("A57").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ASKER
I’m not very good at vba. Could the problem be that i have to somehow set the destination workbook up differently.
Are you runnin the code from your personal macro workbook? If yes, you should use activeworkbook
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
Unless I'm missing something
ASKER CERTIFIED SOLUTION
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.
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.
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\r
'Source Workbook above
strExt = "*.xls*"
strPath = "C:\Users\rascott\Desktop\
strFileName = Dir(strPath & strExt)
While Len(strFileName) > 0
Set wb = Workbooks.Open(Filename:=s
Set VBAwb = ThisWorkbook
'Choose which sheet to copy from
Source.Worksheets("Templat
'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
Is there a way i can add a prompt to ask what source to use and range?
and what cell to paste too.
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\r
'Source Workbook above
strExt = "*.xls*"
strPath = "C:\Users\rascott\Desktop\
strFileName = Dir(strPath & strExt)
While Len(strFileName) > 0
Set wb = Workbooks.Open(Filename:=s
Set VBAwb = ThisWorkbook
'Choose which sheet to copy from
Source.Worksheets("Templat
'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
to
Open in new window
Thomas