VBA_ Copying Excel worksheet to a new workbook without leaving the old Excel reference

엑셀시트를 복사해서 다른 엑셀파일이나 신규생성 엑셀파일로 옮기면 경로문제가 자주 발생합니다.

이전파일(원본)의 파일 경로위치나 외부연결파일(참조)를 그대로 가지고 오면서 제대로 작동하지 않는 경우가 있습니다. 이 때 참조오류가 발생한 시트나 셀을 모두 수정해야 하는 경우가 있어서 번거롭습니다.

이럴 때 활용가능한 매크로 입니다.

https://www.vitoshacademy.com/vba-copying-excel-worksheet-to-a-new-workbook-without-leaving-the-old-excel-reference/

In order to get the beautiful part, looking like the picute below, a few more steps are needed:

VBA

Sub CopyWorksheetsBeautiful()
    
    Dim wks As Worksheet
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    wkb.SaveAs "NewCopyWithReference.xlsx"
    
    ReDim myArr(ThisWorkbook.Worksheets.Count - 1)
    
    Dim i As Long
    For i = 0 To ThisWorkbook.Worksheets.Count - 1
        myArr(i) = ThisWorkbook.Worksheets(i + 1).Name
    Next i
    
    ThisWorkbook.Worksheets(myArr).Copy wkb.Worksheets(Worksheets.Count)
       
End Sub

---

https://www.vitoshacademy.com/vba-copy-worksheet/

VBA

Public Sub CopyWorksheets()
 
    Dim wksCollection As New Collection
    
    wksCollection.Add ThisWorkbook.Worksheets("SheetA")
    wksCollection.Add ThisWorkbook.Worksheets("SheetB")
    wksCollection.Add ThisWorkbook.Worksheets("SheetC")
 
    Dim wks As Worksheet
    Dim newWks As Worksheet
 
    For Each wks In wksCollection
        Dim newName As String
        newName = wks.Name & "_w"
    
        If WorksheetNameIsPresent(newName) Then
            Application.DisplayAlerts = False
            Worksheets(newName).Delete
            Application.DisplayAlerts = True
        End If
    
        wks.Copy after:=Worksheets(Worksheets.Count)
        Set newWks = Worksheets.Item(Worksheets.Count)
    
        With newWks
            .Name = newName
            .Tab.Color = 255
        End With
    Next wks
 
End Sub