Copy paste taking reference of source destination range from input box

[Dee]

I am trying to copy few columns using input box and paste it in the destination cells/range using inputbox. In the below code i am unable to paste it in the destination cell as i am getting "Run time error '438'","Object doesn't support this property or method".

my codes


Sub DFMA_MAKE()

Dim sourcefile As Workbook

Dim source_file As String

Dim myrange As Range

Dim desti_range As Range


source_file = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", Title:="Please choose a file")

Set sourcefile = Workbooks.Open(Filename:=source_file, ReadOnly:=yes)


Set myrange = Application.InputBox _

(Prompt:="Select any range", Title:="Select the range", Type:=8)

myrange.Copy


Workbooks("DFMA Template_P latest.xlsm").Activate

Worksheets("DFMA MAKE").Select

Set desti_range = Application.InputBox _

(Prompt:="Select range", Title:="Select the Destination range", Type:=8)

desti_range.Paste


Range("A2").Select

Application.CutCopyMode = False

sourcefile.Close SaveChanges = no

End Sub


Please direct me to the correct codes.


Thanks in advance,

Dee

 

[S]

Hey Dee,

You are getting that error message as the Copy Mode is getting deactivated when you try selecting the destination range.

You can try using the destination cell as a variable (depends on your requirement)


SS23

 

[Dee]

Hi,

Thanks for your quick response.

I tried with defining destination cell as variable but no luck

Its giving "compile error", "User-defined type not defined".


Dee...

 

[S]

When you used a variable range/cell, did you still use:

[pre]

desti_range.Paste
Then it no more works, but if you just use 


Range("A" & rcount).Select '*see below
activesheet.paste

[/pre]
then it should work.


*rcount or any "x" being a variable can be used- You can also change the column based on your need


S23