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

[crcline]

My inquiry is similiar to thread:
http://www.excelfox.com/forum/showthread.php?124-Copy-Sheets-To-New-Workbook-And-Save&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!