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

[crcline]

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.

[Excel Fox]

So why don't you post these scripts, and we can work towards getting them integrated to one

[Admin]

Hi crcline,

Welcome to ExcelFox !!!

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

[crcline]

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

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

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

[Excel Fox]

Check the attachment....

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

[crcline]

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!

[Excel Fox]

Well, instead of adding a new workbook, you could use the name of the destination file

so replace

Workbooks.Add(xlWorksheet)

with

With Workbooks("NameoFWorkbook.xlsm")

and remove the .Saveas method, and just use a .Save

[crcline]

I tried the recommendation with debugger popping up.

Here's what I did:
1. Removed the following:

        .SaveAs Application.GetSaveAsFilename(FileFilter:="Excel 1997-2010 Files (*.xlsx), *.xlsx"), 51

2. Added the following in its place:

        .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!

[Excel Fox]

Try replacing the second routine with this...

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

[crcline]

Very nice! That's a keeper. THANK YOU!