Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Past Macro Question
Hello,
I want to write a macro that takes certain tabs of a workbook and copies them to a new workbook then breaks all links in the newly created workbook. Can this be done? If so what would the VBA look like? Thanks so much :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Past Macro Question
mydogpeanut
Yes it can be done. Here are two routines that work pretty good. Set Public variables Public LinkPath, Mastr As String Sub CopyTheSheets() Dim DestWB As Workbook Dim ws As Worksheet Dim Wrkbk As String LinkPath = ActiveWorkbook.FullName Mastr = ActiveWorkbook.Name 'Wrkbk = Sheets("Sheet1").Range("B3") 'This should be the name of the new workbook. 'you place the name in a cell as I did.(i.e. Book1.xls) Set DestWB = Workbooks(Wrkbk) For Each ws In ThisWorkbook.Worksheets With DestWB.Worksheets ws.Copy after:=.Item(.Count) Next ws Linkage (LinkPath) 'This will change the link ActiveWorkbook.Save Windows(Mastr).Activate End Sub Function Linkage(link1 As String) 'Undo the links to the Master file and set to current book ActiveWorkbook.ChangeLink link1, ActiveWorkbook.FullName, xlExcelLinks End Function "mydogpeanut" wrote: Hello, I want to write a macro that takes certain tabs of a workbook and copies them to a new workbook then breaks all links in the newly created workbook. Can this be done? If so what would the VBA look like? Thanks so much :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy & Past Macro Question
Thank you... I'm going to give it a try :)
"JRForm" wrote: mydogpeanut Yes it can be done. Here are two routines that work pretty good. Set Public variables Public LinkPath, Mastr As String Sub CopyTheSheets() Dim DestWB As Workbook Dim ws As Worksheet Dim Wrkbk As String LinkPath = ActiveWorkbook.FullName Mastr = ActiveWorkbook.Name 'Wrkbk = Sheets("Sheet1").Range("B3") 'This should be the name of the new workbook. 'you place the name in a cell as I did.(i.e. Book1.xls) Set DestWB = Workbooks(Wrkbk) For Each ws In ThisWorkbook.Worksheets With DestWB.Worksheets ws.Copy after:=.Item(.Count) Next ws Linkage (LinkPath) 'This will change the link ActiveWorkbook.Save Windows(Mastr).Activate End Sub Function Linkage(link1 As String) 'Undo the links to the Master file and set to current book ActiveWorkbook.ChangeLink link1, ActiveWorkbook.FullName, xlExcelLinks End Function "mydogpeanut" wrote: Hello, I want to write a macro that takes certain tabs of a workbook and copies them to a new workbook then breaks all links in the newly created workbook. Can this be done? If so what would the VBA look like? Thanks so much :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Past Macro | Excel Programming | |||
Macro to copy and past a new row when the a row is filled in. | Excel Programming | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) | |||
Copy Past with macro | Excel Programming | |||
Macro to copy and past selected rows only... | Excel Programming |