워크북의 끝에 VBA 복사 시트(숨긴 워크시트 포함)

워크북의 끝에 VBA 복사 시트(숨긴 워크시트 포함)

시트를 복사하여 현재 모든 시트의 끝에 추가합니다(시트가 숨겨져 있는지 여부에 관계없이).

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "copied sheet!"

은 올바르게 시트가 있는 는, 워크시트 됩니다. 이 시트는 마지막 워크시트 뒤에 됩니다.name잘못된 시트의 이름을 바꿉니다.

나는 새로 복사된 것에 대한 참조를 얻기 위해 다음의 변형을 시도했다.WorkSheet단, 성공한 코드나 유효한 코드는 없었습니다.

Dim test As Worksheet
Set test = Sheets(1).Copy(After:=Sheets(Sheets.Count))
test.Name = "copied sheet!"

이거 드셔보세요

Sub Sample()
    Dim test As Worksheet
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "copied sheet!"
End Sub

다시 생각해보면, 더 나은 접근법은

Set test = Sheets(Sheets.Count)

아래 코멘트에서도 올바르게 기술되어 있듯이, 시트의 카피나 이름을 변경할 때 고려해야 할 사항이 많이 있습니다.다른 답변도 확인해 보시기 바랍니다.

복사하기 전에 소스 시트를 표시합니다.그런 다음 복사본이 계속 보이도록 시트를 복사합니다.그러면 복사본이 활성 시트가 됩니다.원하는 경우 원본 시트를 다시 숨깁니다.

저는 다른 워크북에 시트를 복사하다가 비슷한 문제에 직면했습니다.액티비티 시트'는 과거에도 문제가 있었기 때문에 사용을 피하고 싶습니다.그래서 필요에 따라 인라인으로 실행할 수 있는 기능을 작성했습니다.구글을 통해 도착하시는 분들을 위해 다음과 같이 추가하겠습니다.

여기서 가장 중요한 문제는 가시 시트를 마지막 인덱스 위치로 복사하면 Excel이 가시 시트의 끝까지 시트를 재배치한다는 것입니다.따라서 시트를 마지막으로 보이는 시트 뒤의 위치로 복사하면 이 문제가 정렬됩니다.숨겨진 시트를 복사하는 경우에도 마찬가지입니다.

Function Copy_WS_to_NewWB(WB As Workbook, WS As Worksheet) As Worksheet
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook
    
    Dim WSInd As Integer: WSInd = 1
    Dim CWS As Worksheet
    
    'Determine the index of the last visible worksheet
    For Each CWS In WB.Worksheets
        If CWS.Visible Then If CWS.Index > WSInd Then WSInd = CWS.Index
    Next CWS
    
    WS.Copy after:=WB.Worksheets(WSInd)
    Set Copy_WS_to_NewWB = WB.Worksheets(WSInd + 1)

End Function

이 기능을 원래의 질문(즉, 같은 워크북)에 사용하려면 다음과 같은 작업을 수행할 수 있습니다.

Set test = Copy_WS_to_NewWB(Workbooks(1), Workbooks(1).Worksheets(1))
test.name = "test sheet name"

---

-user3598756에서2020년 4월 11일 편집 위 코드의 약간의 리팩터링 추가

Function CopySheetToWorkBook(targetWb As Workbook, shToBeCopied As Worksheet, copiedSh As Worksheet) As Boolean
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook

    Dim lastVisibleShIndex As Long
    Dim iSh As Long

    On Error GoTo SafeExit
    
    With targetWb
        'Determine the index of the last visible worksheet
        For iSh = .Sheets.Count To 1 Step -1
            If .Sheets(iSh).Visible Then
                lastVisibleShIndex = iSh
                Exit For
            End If
        Next
    
        shToBeCopied.Copy after:=.Sheets(lastVisibleShIndex)
        Set copiedSh = .Sheets(lastVisibleShIndex + 1)
    End With
    
    CopySheetToWorkBook = True
    Exit Function
    
SafeExit:
    
End Function

리팩터링에서는 다른 변수 이름(좀 더 설명적인?)을 사용하는 것 외에 다음과 같은 사항을 취급합니다.

1. 함수 파라미터에 반환된(복사된) 워크시트를 포함하면서 함수 타입을 Boolean으로 변환하여 호출 Sub가 다음과 같은 오류를 발생시킬 수 있도록 합니다.

    Dim WB as Workbook: Set WB = ThisWorkbook ' as an example
    Dim sh as Worksheet: Set sh = ActiveSheet ' as an example
    Dim copiedSh as Worksheet
    If CopySheetToWorkBook(WB, sh, copiedSh) Then
        ' go on with your copiedSh sheet
    Else
        Msgbox "Error while trying to copy '" & sh.Name & "'" & vbcrlf & err.Description
    End If
   

2. 마지막 시트 인덱스에서 다음 루프가 뒤로 이동하고 처음에 보이는 시트가 발생하므로 "마지막"으로 보이는 시트의 다음 루프가 발생합니다.

@Siddharth Route의 코드에 근거해 다음의 코드를 사용하는 경우는, 활성화 여부에 관계없이, 카피된 시트의 이름을 변경할 수 있습니다.

Sub Sample()

    ThisWorkbook.Sheets(1).Copy After:=Sheets(Sheets.Count)
    ThisWorkbook.Sheets(Sheets.Count).Name = "copied sheet!"

End Sub

이 코드를 선두에 추가합니다.

    Application.ScreenUpdating = False
     With ThisWorkbook
      Dim ws As Worksheet
       For Each ws In Worksheets: ws.Visible = True: Next ws
     End With

이 코드를 끝에 추가합니다.

    With ThisWorkbook
     Dim ws As Worksheet
      For Each ws In Worksheets: ws.Visible = False: Next ws
    End With
     Application.ScreenUpdating = True

첫 번째 시트보다 더 많은 시트를 활성화하고 표시하려면 마지막에 코드를 조정합니다.예를 들어 다음과 같습니다.

     Dim ws As Worksheet
      For Each ws In Worksheets
       If ws.Name = "_DataRecords" Then

         Else: ws.Visible = False
       End If
      Next ws

새 시트의 이름이 변경되었는지 확인하려면 다음과 같이 코드를 조정하십시오.

     Sheets(Me.cmbxSheetCopy.value).Copy After:=Sheets(Sheets.Count)
     Sheets(Me.cmbxSheetCopy.value & " (2)").Select
     Sheets(Me.cmbxSheetCopy.value & " (2)").Name = txtbxNewSheetName.value

이 코드는 특정 시트(드롭다운박스에서 선택)를 원하는 형식과 수식으로 새 시트에 복사한 다음 사용자 입력으로 새 시트의 이름을 바꿀 수 있는 사용자 양식에서 가져온 것입니다.시트를 복사할 때마다 "(2)"로 지정된 이전 시트 이름이 자동으로 지정됩니다. 예: "OldSheet"은 복사 후 및 이름 변경 전에 "OldSheet (2)"가 됩니다.따라서 이름을 바꾸기 전에 프로그램 이름이 지정된 복사 시트를 선택해야 합니다.

"mySheet"라는 이름의 시트를 복사하여 사용하는 경우.복사 후:=, Excel은 먼저 복사된 시트의 이름을 정확히 동일하게 지정하고 '(2)'만 추가하면 최종 이름은 "mySheet(2)'입니다.

숨겼든 안 숨겼든 상관없습니다.코드 2줄로 흔들리고 워크북 끝에 복사된 시트를 추가합니다!!!

예:

Sheets("mySheet").Copy After:=Sheets(ThisWorkbook.Sheets.count)
Sheets("mySheet (2)").name = "TheNameYouWant"

간단히 말하면 안 돼!

첫 번째 시트가 표시되지 않을 경우 시도합니다.

Sheets(1).Visible = True Sheets(1).Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).name = "copied sheet!" Sheets(1).Visible = False

답변: 이것을 발견하여 당신과 공유하고 싶습니다.

Sub Copier4()
   Dim x As Integer

   For x = 1 To ActiveWorkbook.Sheets.Count
      'Loop through each of the sheets in the workbook
      'by using x as the sheet index number.
      ActiveWorkbook.Sheets(x).Copy _
         After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
         'Puts all copies after the last existing sheet.
   Next
End Sub

하지만 문제는 시트 이름을 바꿀 때 다음 코드와 함께 사용할 수 있는가 하는 것입니다.그렇다면 어떻게 이름을 바꿀 수 있는가?

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

언급URL : https://stackoverflow.com/questions/11990465/vba-copy-sheet-to-end-of-workbook-with-hidden-worksheets