VBA copy data from a workbook to another

[mark692]

Good day sir i have two workbooks

1. workbookSource- where we will get the data

2. workbookPastehere- where the data will be pasted


what code will i use if i want data from workbookSource column A and Column D to be paste in workbookPastehere Sheet INPUT, column B and column F,

i start in using this VBA code

Sub CopyPaste()
Selection.copy
Workbooks.Open Filename:="C:\Users\Record\workbookPastehere.xlsm"
Sheets("Input").Select
Activesheet.paste
Application.cutcopymode = False


End sub

this code work if the workbookPastehere.xlsm is not yet open, problem occur when workbookPastehere.xlsm is already open an error "workbookPastehere.xlsm is already open. Reopening will cause". i want a code that will not reopen the workbookPastehere.xlsm if its already open. thanks

 

[Fluff]

How about

Sub chkFileOpen()
   Dim Fname As String
   Dim pth As String
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   If Not wbkExists(Fname) Then Workbooks.Open pth & Fname

End Sub
Public Function wbkExists(wbkname As String) As Boolean
   On Error Resume Next
   wbkExists = (LCase(Workbooks(wbkname).Name) = LCase(wbkname))
   On Error GoTo 0
End Function

Change Pths & Fname to suit

 

[mark692]

How about

Sub chkFileOpen()
   Dim Fname As String
   Dim pth As String
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   If Not wbkExists(Fname) Then Workbooks.Open pth & Fname

End Sub
Public Function wbkExists(wbkname As String) As Boolean
   On Error Resume Next
   wbkExists = (LCase(Workbooks(wbkname).Name) = LCase(wbkname))
   On Error GoTo 0
End Function

Change Pths & Fname to suit

great it works, the error reopen is not apearing but how can i insert the copy column code :D im not familiar on that kind of code :D

 

[Fluff]

How about

Sub chkFileOpen()
   Dim Wbk As Workbook
   Dim Fname As String
   Dim pth As String
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   Selection.Copy
   If Not wbkExists(Fname) Then
      Set Wbk = Workbooks.Open(pth & Fname)
   Else
      Set Wbk = Workbooks(Fname)
   End If
   Wbk.Sheets("Input").Range("A1").Paste

End Sub

 

[mark692]

this object doesnt support object or method on
"Wbk.Sheets("Input").Range("A1").Paste"

 

[Fluff]

Sorry, it should be pastespecial

 

[mark692]

thanks i already tried it, it paste the range that is selected from the source workbook,, is there a way were i can set a specific range that will be copied to the file in "Fname="? example i want to copy column A and column B, and paste it in column A and column B of the file in "Fname"?

 

[Fluff]

Try

Sub chkFileOpen()
   Dim wbk As Workbook
   Dim Fname As String
   Dim pth As String
   Dim Addr As String
   
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   Selection.Copy
   Addr = Selection.Address
   If Not wbkExists(Fname) Then
      Set wbk = Workbooks.Open(pth & Fname)
   Else
      Set wbk = Workbooks(Fname)
   End If
   wbk.Sheets("Input").Range(Addr).PasteSpecial

End Sub

 

[mark692]

Try

Sub chkFileOpen()
   Dim wbk As Workbook
   Dim Fname As String
   Dim pth As String
   Dim Addr As String
   
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   Selection.Copy
   Addr = Selection.Address
   If Not wbkExists(Fname) Then
      Set wbk = Workbooks.Open(pth & Fname)
   Else
      Set wbk = Workbooks(Fname)
   End If
   wbk.Sheets("Input").Range(Addr).PasteSpecial

End Sub

hmm this code still needs to select column A and column B, i want column a and column B automaticaly selected and copy then paste to the Fname file

 

[Fluff]

How about

Sub chkFileOpen()
   Dim wbk As Workbook
   Dim Fname As String
   Dim pth As String
   Dim Addr As String
   
   pth = "C:\Mrexcel\"
   Fname = "Book1.xlsm"
   Range("A:B").Copy
   Addr = Selection.Address
   If Not wbkExists(Fname) Then
      Set wbk = Workbooks.Open(pth & Fname)
   Else
      Set wbk = Workbooks(Fname)
   End If
   wbk.Sheets("Input").Range("A1").PasteSpecial

End Sub