통합문서의 각 시트 값 복사하기

Sub copyWorkbook()
    Dim currentSheet As Worksheet
    Dim activeSheet As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim currentCell As Range
    Dim targetCell As Range
    
    ' 현재 시트
     Set currentSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' 활성 시트
    Set activeSheet = Workbooks("workbook.xlsx").Worksheets("sheet1" )
    
    ' 현재 시트와 동일한 구조의 활성 시트에서 값을 가져오기
    For Each rng In currentSheet.Range("A4:R25")
        Set currentCell = rng.Cells
        Set targetCell = activeSheet.Cells(currentCell.Row, currentCell.Column)
        
        ' 가져온 값을 현재 시트의 동일한 위치에 넣기
        targetCell.Value = currentCell.Value
    Next rng
End Sub

 

복사할 시트가 여러개이면서 범위가 다른경우

Sub CopyCellValues()
    Dim currentSheet As Worksheet
    Dim activeSheet As Worksheet
    Dim rng As Range
    Dim currentCell As Range
    Dim targetCell As Range
    
    ' 현재 시트
   ' Set currentSheet = ActiveSheet
    
    ' 현재 워크북
    Dim wb As Workbook
    Set wb = ThisWorkbook

 

    ' 대상 워크북
    Dim twb As Workbook
    Set twb = Workbooks("workbook.xlsx")
    
    ' 모든 시트에 대해 반복

For Each currentSheet In twb.Worksheets
    For Each activeSheet In wb.Worksheets
        ' 대상 범위와 복사할 데이터의 범위 설정

       if currentSheet.name = activeSheet.name then
        Dim targetRange As Range
        Dim sourceRange As Range
        
        ' 각 시트에 따라 대상 범위와 복사할 데이터의 범위 설정
        Select Case activeSheet.Name
            Case "Sheet1"
                Set targetRange = activeSheet.Range("A4:R25")
                Set sourceRange = currentSheet.Range("A4:R25")
            Case "Sheet2"
                Set targetRange = activeSheet.Range("B1:H22")
                Set sourceRange = currentSheet.Range("B1:H22")
            Case "Sheet3"
                Set targetRange = activeSheet.Range("R6:T55")
                Set sourceRange = currentSheet.Range("R6:T55")
            Case "Sheet4"

            ' union으로 범위 합치기
                Set targetRange = Union(activeSheet.Range("R6:S10"), activeSheet.Range("T6:V10"))
                Set sourceRange = Union(currentSheet.Range("R6:S10"), currentSheet.Range("T6:V10"))
            ' 추가적인 시트에 대한 대상 범위와 복사할 데이터의 범위 설정
            ' Case "Sheet5"
            '     Set targetRange = activeSheet.Range("...")
            '     Set sourceRange = currentSheet.Range("...")
            ' Case "Sheet6"
            '     Set targetRange = activeSheet.Range("...")
            '     Set sourceRange = currentSheet.Range("...")
            ' ...
            ' 필요에 따라 추가 시트와 대상 범위, 복사할 데이터의 범위를 설정
            
            ' 기본값: 대상 범위와 복사할 데이터의 범위를 설정하지 않은 시트는 건너뜀
            Case Else
                Continue For
        End Select
        
        ' 가져온 값을 대상 범위에 복사하기
        targetRange.Value = sourceRange.Value

      endif
    Next activeSheet

next currentSheet 
End Sub