CODEWITHSUNDEEP
excelvba
Aleksa456
Aleksa456

Reputation: 1

Copy sheets from one worksheet to another via loop

I have two files. 1 file contains data with tabs named as company. The second file is to analyse the companies and I have there also tabs which are named in the same name as in tabs in file with copmanies data. In the file where I analyse data I have tab macro where I put information requires for macro. Companies name, file names. When the new copamny comes or the old one will disappear I want to do the same in macro as macro takes information from the tab macro from cells. Now what I want to have is that macro will copy for company A from file with companies data and paste into file with companies analyse. I have used to that loop FOR TO as then macro will copy and paste company A and then B,then C and so on and so forth. The macro is below. First part works. Opens file with data and active however then it doesnt work. I think I mixed variables but I have no idea how to fix it. Any ideas?

Sub CopyData()

Workbooks.Open Range("A10").Value

Dim wb As Workbook

Dim wbk As Workbook

Dim i As Integer

Dim FieldAVal As Worksheet

Dim FieldBVal As Worksheet

Dim Iter As Integer

 

For Each wb In Application.Workbooks

    If wb.Name Like "*Reconciliation*" Then

        wb.Activate

        Exit For

    End If

Next wb

 

Set wbk = Workbooks(Range("A9").Value)

    Sheets("Macro").Select

    Range("B6").Select

    'define ranges with column numbers

   

Iter = Cells(1, 3).Value


    

                    

             For i = 1 To Iter

                FieldAVal.Name = Cells(i + 14, 2).Value

                FieldBVal.Name = Cells(i + 14, 3).Value

                

                

          

                Workbooks(wbk).Worksheets(FieldBVal).Range("A1:V1000").Copy _

Destination:=ThisWorkbook.Worksheets(FieldAVal).Range("B2")

               

 

                Next i

End Sub

Upvotes: 0

Views: 88

Answers (1)

Sacru2red
Sacru2red

Reputation: 64

I am not sure understanding

Public Sub CopyData()
    On Error GoTo ErrHANDLER
    Dim wb As Workbook
    Dim wbk As Workbook
    Dim i As Integer
    Dim FieldAVal As Worksheet
    Dim FieldBVal As Worksheet
    Dim Iter As Integer
    'add Variables
    Dim secondFileName As String
    Dim wbSecondFile As Workbook
    Dim openedworkbookNameB As String
    Dim openedworkbook As Worksheet
    Dim pasteWorksheet As Worksheet
    
    secondFileName = Range("A10").Value
    'already opened workbook
    openedworkbookNameB = Range("A9").Value
    
    Set wbSecondFile = Workbooks.Open(secondFileName)
    'Fail to open
    If wbSecondFile Is Nothing Then
        Exit Sub
    End If
    Set openedworkbook = Workbooks(openedworkbookNameB)
    'no workbook
    If openedworkbook Is Nothing Then
        Exit Sub
    End If
    
    Call ThisWorkbook.Activate
    ThisWorkbook.Sheets("Macro").Select
    'ActiveSheet == "Macro" sheet
    ActiveSheet.Range("B6").Select
    Iter = VBA.Val(ActiveSheet.Cells(1, 3).Value)
    For i = 1 To Iter
        FieldAVal.name = ActiveSheet.Cells(i + 14, 2).Value
        FieldBVal.name = ActiveSheet.Cells(i + 14, 3).Value
        
        Set pasteWorksheet = ThisWorkbook.Worksheets(FieldAVal)
        If Not pasteWorksheet Is Nothing Then
            openedworkbook.Worksheets(FieldBVal).Range("A1:V1000").Copy _
                Destination:=pasteWorksheet.Range("B2")
        End If
        Set pasteWorksheet = Nothing
    Next i
    Exit Sub
ErrHANDLER:
    'When Raise error
    Debug.Print Err.Number & " : " & Err.Description
    'debug point Here
    'press "F8" Key to Run a Macro Line by Line
    Stop
    Resume
End Sub

Upvotes: 0

Related Questions