Announcement

Collapse
No announcement yet.

Copy Workbook Values To Existing Workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy Workbook Values To Existing Workbook



    Trust me when I say this is a last resort to ask for help. However, not being a VBA guy, I'm at a loss. I have figured out most of what I need, but i can't figure out how to make the following post only values. Every combination I use doesn't work. I'm sure it's something simple, and appreciate anyone who can help!!!

    Code:
    Sub CopyValues()
       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)
    
    
        SrcWbk.Sheets("JUL").Range("b4:ai17").Copy DestWbk.Sheets("62nd").Range("b4:ai17")
    
    
    SrcWbk.Close False
    End Sub

  • #2
    you could try something like this:

    Code:
    Sub CopyValues()
        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)
        DestWbk.Sheets("62nd").Range("b4:ai17").Value = SrcWbk.Sheets("JUL").Range("b4:ai17").Value
        SrcWbk.Close False
    End Sub

    Comment


    • #3
      I wouldn't define a variable for the destination workbook in this case. ThisWorkBook will always refer to the workbook that contains the code.


      Code:
      Option Explicit
      
      Sub CopyValues()
          Dim SrcWbk As Workbook
          Dim Fname As String
      
      
          Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
          If Fname = "False" Then Exit Sub
          Set SrcWbk = Workbooks.Open(Fname)
      
          SrcWbk.Sheets("JUL").Range("b4:ai17").Copy
          ThisWorkbook.Sheets("62nd").Range("b4:ai17").PasteSpecial xlValues
          SrcWbk.Close False
      
      End Sub
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4


        So sorry I forgot to thank you for this. Helped a ton!!

        Comment

        Working...
        X