John.McLaughlin
Board Regular
- Joined
- Jul 19, 2011
- Messages
- 169
Hello,
I am trying to copy a range from one workbook to another, and getting a runtime error? Upon researching this, it suggests its a bug after a security update and I should delete MSForms.exd?
Before I delete any files, I wanted to know if possibly this is an error in the VBA?
Below is the line that stops the code with the Runtime Error 438. Object doesn't support this property or method.
wbTarget.Range("A50").Select
Thanks for your help!
JM
I am trying to copy a range from one workbook to another, and getting a runtime error? Upon researching this, it suggests its a bug after a security update and I should delete MSForms.exd?
Before I delete any files, I wanted to know if possibly this is an error in the VBA?
Below is the line that stops the code with the Runtime Error 438. Object doesn't support this property or method.
wbTarget.Range("A50").Select
Thanks for your help!
JM
Code:
Sub ScheduleUPDATE()
'
' ScheduleUPDATE Macro
'
'
' Copy open items to sheet.
'
'
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbThis As Workbook 'workbook from where the data is to copied
Dim strName As String 'name of the source sheet/ target workbook
'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook
'unhide scheduled sheet and select it
Sheets("SCHEDULED").Visible = True
Sheets("SCHEDULED").Select
'get the active sheetname of the book
strName = ActiveSheet.Name
'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("C:\data\" & strName & ".xlsm")
'select cell A50 on the target book
wbTarget.Range("A50").Select
'clear existing values form target book
wbTarget.Range("A50:G50").ClearContents
'activate the source book
wbThis.Activate
'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
'copy the range from source book
wbThis.Range("A2:G2").Copy
'paste the data on the target book
wbTarget.Range("A50").PasteSpecial
'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
'save the target book
wbTarget.Save
'close the workbook
wbTarget.Close
'activate the source book again
wbThis.Activate
'clear memory
Set wbTarget = Nothing
Set wbThis = Nothing
'
End Sub