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 :)

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 :)

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 :)