Results 1 to 10 of 10

Thread: Copy Existing Sheet, Paste Values from existing workbook into new workbook

  1. #1
    Junior Member
    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0

    Copy Existing Sheet, Paste Values from existing workbook into new workbook

    My inquiry is similiar to thread:
    http://www.excelfox.com/forum/showth...&p=384#post384

    Here are the variants:
    1. I have a macro (script #1) that allows the user (me) to invoke the Microsoft Directory, select the file & lists out the sheets in the selected file. It's nice b/c the folder structure changes every month.
    2. I have another macro (script #2) that is hard coded on the path location, file and sheet name to select and copy from the source into the destination workbook.
    3. Yet another macro (script #3) that takes the destination workbook and copy / paste special values since there are external references (links) to other workbooks.

    Here's what I'd like to do:
    Have one macro that performs the functions of scripts 1-3 above AND saves the output into the a separate version. Can someone please help? Still learning the VBA world.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    So why don't you post these scripts, and we can work towards getting them integrated to one
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi crcline,

    Welcome to ExcelFox !!!

    Could you post the VBA codes here that you have ? (Use code tags while posting the code)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0

    File Samples

    Here's a sample of files & code - I've tried to preserve the code so that the original source is footnoted:
    1. Script #1 (Attached File) Uses the windows directory to select a file. I'd like this functionality in selecting the file & then the sheet within the file selection
    2. Script #2
    Code:
    Sub InsertTBLINE()
    'Developed by Casey Cline, 2011.
    'Opens source file. Currently path & file name must be manually updated monthly.
    Workbooks.Open ("J:\2011 Month End\2011 Reporting\\2011-07\2011-07 Recon_08.04.xlsx")
    
    'copy sheet from another workbook before first tab of Book2
    Workbooks("2011-07 Recon_08.04.xlsx").Sheets("TB 07.31.11").Copy After:=Workbooks("Reporting_v1.xlsm").Sheets("Sheet1")
    'Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Sheet1"
    
    'close the source workbook without saving any changes
     Workbooks("2011-07 Recon_08.04.xlsx").Close
          
    End Sub
    Script #3
    Code:
    Sub Link2Value()
       Dim rng As Range
       For Each rng In ActiveSheet.UsedRange.Cells
          If rng.HasFormula Then
             If InStr(rng.Formula, "\[") Then
                rng.Value = rng.Value
             End If
          End If
       Next rng
    End Sub
    Attached Files Attached Files

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Check the attachment....

    Code:
    Option Explicit
    Dim strFile As String
    Dim wbk As Workbook
    Sub GetFileAndSaveSheetToAnotherFileAndSaveAsValues()
    
        Dim wks As Worksheet
            
        strFile = Application.GetOpenFilename("Excel 1997-2010 Files (*.xls*), *.xls*")
        If strFile <> "False" Then
            Set wbk = Workbooks.Open(strFile, 0, 1)
            strFile = ""
            For Each wks In wbk.Worksheets
                If wks.Visible Then
                    strFile = strFile & wks.Name & "|"
                End If
            Next wks
            If strFile <> "" Then
                strFile = Left(strFile, Len(strFile) - 1)
            End If
            frmSheetSelector.lstSheets.List = Split(strFile, "|")
            frmSheetSelector.Show
        End If
        Set wks = Nothing
        
    End Sub
    
    Sub GetSheetDataToNewWorkbook(strSheetName As String)
    
        Dim varArray
        varArray = wbk.Sheets(strSheetName).UsedRange.Value
        With Workbooks.Add(xlWorksheet)
            .Sheets(1).Cells(1).Resize(UBound(varArray, 1), UBound(varArray, 2)).Value = varArray
            .SaveAs Application.GetSaveAsFilename(FileFilter:="Excel 1997-2010 Files (*.xlsx), *.xlsx"), 51
            .Close 0
        End With
        wbk.Close
        
        Set wbk = Nothing
        strFile = vbNullString
        Unload frmSheetSelector
        
    End Sub
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0
    Thanks - that is very nice!

    Is there a way to have the source file (the file/sheet) I select copy into a pre-existing file (destination file)? I have a 'container' file that contains various reporting from a variety of workbooks which I capture into one file (a reporting file).

    Also, I would like the copy/paste values function to execute on the reporting file (destination file). How do I change the code to make this happen?

    Again, thank you!

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Well, instead of adding a new workbook, you could use the name of the destination file

    so replace
    Code:
    Workbooks.Add(xlWorksheet)
    with
    Code:
    With Workbooks("NameoFWorkbook.xlsm")
    and remove the .Saveas method, and just use a .Save
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Junior Member
    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0
    I tried the recommendation with debugger popping up.

    Here's what I did:
    1. Removed the following:
    Code:
            .SaveAs Application.GetSaveAsFilename(FileFilter:="Excel 1997-2010 Files (*.xlsx), *.xlsx"), 51
    2. Added the following in its place:
    Code:
            .Copy After:=ActiveWorkbook.Sheets("Sheet1")
    'Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Sheet1"
    Since I am running this macro from WITHIN the reporting workbook, I want to grab the source and copy the selected worksheet before "Sheet1" which I have in my reporting workbook as a placeholder to drop the source sheet after.

    It's really close but still getting the 'debugger' - any recommendations to close this loop?

    Again, thanks for the help!

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try replacing the second routine with this...

    Code:
    Sub GetSheetDataToNewWorkbook(strSheetName As String)
    
        Dim varArray
        varArray = wbk.Sheets(strSheetName).UsedRange.Value
        With ThisWorkbook
            .Sheets.Add Before:=.Sheets(1)
            .Sheets(1).Cells(1).Resize(UBound(varArray, 1), UBound(varArray, 2)).Value = varArray
            .Save
        End With
        wbk.Close
        
        Set wbk = Nothing
        strFile = vbNullString
        Unload frmSheetSelector
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Junior Member
    Join Date
    Sep 2011
    Posts
    5
    Rep Power
    0
    Very nice! That's a keeper. THANK YOU!

Similar Threads

  1. Replies: 2
    Last Post: 05-28-2013, 05:32 PM
  2. VBA Code to Open Workbook and copy data
    By Howardc in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 06:58 PM
  3. VBA code to copy data from source workbook
    By Howardc in forum Excel Help
    Replies: 1
    Last Post: 07-30-2012, 09:28 AM
  4. Replies: 2
    Last Post: 04-08-2012, 09:42 AM
  5. Copy Sheets To New Workbook And Save
    By Prabhu in forum Excel Help
    Replies: 5
    Last Post: 09-06-2011, 09:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •