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.

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

Hi crcline,

Welcome to ExcelFox !!!

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

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

---

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

---

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!

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

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!

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

---

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