How to copy selection.address cell values to another workbook.?

[miqbal]

hi,
I am new to VBA and tryin to reduce some workload to make a small VBA program to copy range randomly with mouse and paste it to destination file.
source file is "Mytest.xlsm" and destination file is "BatchEntry.xlsx"

I can easily copy range by below code and successfully paste It to other sheet but when I tried to paste it to another workbook it gives me error.

so below is the code I ran it in source file which is "Mytest.xlsm"

Sub copytoBatchEntry()

Set areatocopy = Sheet1.Range(Selection.Address)
areatocopy.Copy

'sheet2.range("a2").PasteSpecial ---- it works without any error for current workbook's worksheet.

Workbooks.Open ("U:\Batch Entry.xlsx")
Windows("Batch Entry.xlsx").Activate

Windows("Batch Entry.xlsx").Sheets("sheet1").Range("a2").PasteSpecial
End Sub


the above code gives error
"Run Time Error: 438 object doesn't support this property or method."

Please if someone can help to point out what I am doing wrong here.

 

[pmich]

Please try this.
I have used the suggestion of Andrew Poulsom

Sub copytoBatchEntry()
 Dim wkbDest As Workbook
 Dim wkbSource As Workbook
 Set wkbSource = ThisWorkbook
 Set wkbDest = Workbooks.Open("U:\Batch Entry.xlsx")
 wkbSource.Activate
 wkbDest.Sheets("sheet1").Range("a2").Value = wkbSource.Sheets("Sheet1").Range(Selection.Address).Value
end sub

 

[miqbal]

thanks for the help but it only paste 1st cell value of "Selection.Address" to the "wkbDest.Sheets("sheet1").Range("a2").value".

i want to use "Copy" and "PasteSpecial" method. Tried several techniques but still not working.

 

[pmich]

Please see these links:
https://www.mrexcel.com/forum/excel...special-vba.html?highlight=copy+paste+special
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel

 

[pmich]

I recorded a macro and got this code.

Sub copytoBatchEntry()

Set areatocopy = Sheet1.Range(Selection.Address)
areatocopy.Copy
'sheet2.range("a2").PasteSpecial ---- it works without any error for current workbook's worksheet.

Workbooks.Open ("U:\Batch Entry.xlsx")
Windows("Batch Entry.xlsx").Activate

'https://www.mrexcel.com/forum/excel-questions/1046742-help-paste-special-vba.html?highlight=copy+paste+special
'https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=False

End Sub

 

[pmich]

I have edited the previous code I gave in #2 and this works.

Sub copytoBatchEntry()
 Dim wkbDest As Workbook
 Dim wkbSource As Workbook
 Set wkbSource = ThisWorkbook
 Set wkbDest = Workbooks.Open("U:\Batch Entry.xlsx")
 wkbSource.Activate
 wkbSource.Sheets("Sheet1").Range(Selection.Address).Copy
  wkbDest.Activate
 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
   False, Transpose:=False
end sub

 

[miqbal]

Thanks "pmich".
appreciate your help.
that code kind of work but still not efficient.
code only works when destination workbook is current not opened. it successfully execute the code by first opening the destination workbook and than insert all the selection.address values.
but I want it to work dynamically.. means
if destination workbook is closed then open the closed workbook and insert values
elseif destination workbook is currently open then activate the workbook and overwrite values or delete previous values and paste new values.

I tried above code but it only works with closed destination workbook.

 

[pmich]

Insert a MODULE. Paste the code given below in the MODULE.

Function Check_Open_File(Mypath As String, MyFyl As String)
 'http://www.bigresource.com/Tracker/Track-vb-xUjOxB056j/
 Dim wbkTemp As Workbook
 Dim IsMyFilOpened As Boolean
 For Each wbkTemp In Application.Workbooks
  If wbkTemp.Name = MyFyl Then
   If ActiveWorkbook.Name <> MyFyl Then
    wbkTemp.Activate
   End If 'ActiveWorkbook.Name <> MyFyl
   IsMyFilOpened = True
  End If 'wbkTemp.Name = MyFyl
  If IsMyFilOpened = True Then
    Exit For
   End If
 Next
 If IsMyFilOpened = False Then
  Application.DisplayAlerts = False
  Workbooks.Open Filename:=Mypath & MyFyl
  Application.DisplayAlerts = True
 End If 'IsMyFilOpened = False
End Function

Edit your code in your UserForm as given below.

Private Sub copytoBatchEntry()
 Dim wkbDest As Workbook
 Dim wkbSource As Workbook
 Dim FylOpnd As String
 Set wkbSource = ThisWorkbook
 wkbSource.Activate
 wkbSource.Sheets("Sheet1").Range(Selection.Address).Copy
 FylOpnd = Check_Open_File("U:\", "Batch Entry.xlsx")
 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=False
End Sub

 

[miqbal]

finaly I figure it out.

Sub copytoBatchEntry()
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbSource = ThisWorkbook
Set wkbDest = Workbooks.Open("U:\Batch Entry.xlsx")
wkbDest.Sheets("sheet1").Rows(2 & ":" & Sheet1.Rows.Count).Delete
    wkbSource.Activate
    wkbSource.Sheets("Sheet1").Range(Selection.Address).Copy
    
    wkbDest.Activate
    wkbDest.Sheets("sheet1").Range("a2").Select
        
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
wkbDest.Save

End Sub

now its working without any error.

 

[miqbal]

thanks "pmich" you helped me a lot.