• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open any Excel file then Copy data from the file where the code resides into the newly opened file

Iain A

New Member
Code:
Sub CreateDCT()

'I have an open Excel file (A) where the macro is running from, i then want to open another excel file (B) and copy data from (A) and Paste into (B)

   Dim Fname As String
   Dim SrcWbk As Workbook
   Dim DestWbk As Workbook

   Set DestWbk = ThisWorkBook

   Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
   If Fname = "False" Then Exit Sub
   Set SrcWbk = Workbooks.Open(Fname)

   'Fails on this line
   SrcWbk.Sheets("test sheet with data").Range("A1:D6").Copy DestWbk.Sheets("data from test sheet").Range("A1:D6")

   SrcWbk.Close False


End Sub
 
Last edited:
Iain A
Please, reread Forum Rules form
Posting Rules & Etiquette
How to get the Best Results at Chandoo.org

eg Please post, new posts in the correct forums, not as Emails/Messages to people
 
Code:
Sub CreateDCT()

'Select and open any excel file then copy 'test sheet with data' from Open file where code resides, then paste into the newly opened file "data from test sheet".
Sheets("sheet where data is copied from").Select
Range("A:X").Copy 'define the range to copy

   Dim Fname As String
   Dim SrcWbk As Workbook
   Dim DestWbk As Workbook
  
   Set DestWbk = ThisWorkBook
  
  
  
   Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
   If Fname = "False" Then Exit Sub
   'Set SrcWbk = Workbooks.Open(Fname)
   Set DestWbk = Workbooks.Open(Fname)

 Sheets("sheet in selected workbook where data is pasted to").Select
Range("A:X").PasteSpecial xlPasteAll

'   SrcWbk.Close False




End Sub
Code:
Sub CreateDCT()

'I have an open Excel file (A) where the macro is running from, i then want to open another excel file (B) and copy data from (A) and Paste into (B)

   Dim Fname As String
   Dim SrcWbk As Workbook
   Dim DestWbk As Workbook

   Set DestWbk = ThisWorkBook

   Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
   If Fname = "False" Then Exit Sub
   Set SrcWbk = Workbooks.Open(Fname)

   'Fails on this line
   SrcWbk.Sheets("test sheet with data").Range("A1:D6").Copy DestWbk.Sheets("data from test sheet").Range("A1:D6")

   SrcWbk.Close False


End Sub
 
Back
Top