CODEWITHSUNDEEP
excelvba
Aviran
Aviran

Reputation: 1

how to copy data from one sheet to another in different workbooks by sheet name using loops

i want to copy data from worksheets in workbook "Miz" to worksheets in workbook "Prime" by the worksheets names. meaning, i want the data from worksheet "assets" in Miz to be copied to worksheet "assets" in workbook "Prime" by loop (cause i have many worksheets) and so on for other worksheets.

p.s i got the code to work but it doesn't loop through all the sheets. it only copies the first one and that's it.

 Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     Dim WsSource As Workbook
     Dim WsTarget As Workbook
     Dim LastCell As Variant

     Set WsSource = Workbooks("Prime.xlsm")
     Set WsTarget = Workbooks("Miz.xlsm")
     
     WsTarget.activate
     WS_Count = ActiveWorkbook.Worksheets.Count

     For I = 1 To WS_Count
     
       If ActiveWorkbook.Worksheets(I).Name = WsSource.Worksheets(I).Name Then
         
         WsTarget.activate
         
          LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
          ActiveSheet.Range("A1", LastCell).Select
          Selection.Copy
        
        WsSource.activate
        ActiveWorkbook.Worksheets(I).activate
        Range("F1").Select
        Selection.PasteSpecial Paste:=xlPasteAll

        End If
       
         
         Next I

  End Sub

Upvotes: 0

Views: 66

Answers (1)

GMalc
GMalc

Reputation: 2628

This could be done better, but i'm tired. The code loops through each workbook and copies the used range of the source workbook to the destination workbook range F1. Both workbooks must be open, or else you will receive the Subscript out of range error.

Sub WsLoop()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim WsSource As Workbook
Dim WsTarget As Workbook
Dim Rng As Range

Set WsSource = Workbooks("Miz.xlsm")
Set WsTarget = Workbooks("Prime.xlsm")

    For Each ws In WsSource.Sheets
        Set Rng = ws.UsedRange

        For Each ws1 In WsTarget.Sheets
            If ws.Name = ws1.Name Then
                Rng.Copy Destination:=ws1.Range("F1")
            End If
        Next ws1
    Next ws

End Sub

Upvotes: 1

Related Questions