Excel 2010: Copy range from one workbook to another

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

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

 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You need to specify the sheet.
Code:
wbTarget.Sheets(1).Range("A50").Select
Change Sheets(1) to your actual sheet name, ie. Sheets("Data"), etc.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top