copy from one workbook to another using loop

[aleksandra123]

Hi Guys, I have developed the below code however it shows me the error here. When it comes to part that the macro should copy data from one workbook to another.

>>> use code - tags <<<

Workbooks(wbk).Worksheets(FieldBVal).Range("A1:V1000").Copy Workbooks(recon).Worksheets(FieldAVal).Range("B2")

Just shortly what the macro should do. It should simply copy sheets from one workbook to another. Each sheet refers to one company so it has to be past to another workbook also to the worksheet with the same name of the company. Therefore, I have decided to put name of sheets into excel where is macro. It can happen copmpanies will be added , removed so the user can easily change the name of worksheets or add the new one (without knowing macro structure) but unfortunately sth doesnt work. Can anyone help me out?

>>> use code - tags <<<

Sub Copy data()

Workbooks.Open Range("A10").Value

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)
Set recon = Workbooks(Range("A11").Value)


    Sheets("Macro").Select
    Range("B6").Select
  
    Dim i As Integer
    Dim FieldAVal As String
    Dim FieldBVal As String

    Dim Iter As Integer
    Iter = Cells(1, 3).Value

  
                   
             For i = 1 To Iter
                FieldAVal = Cells(i + 5, 2).Value
                FieldBVal = Cells(i + 5, 3).Value
                 'SAP code to be executed for each row
              
Workbooks(wbk).Worksheets(FieldBVal).Range("A1:V1000").Copy Workbooks(recon).Worksheets(FieldAVal).Range("B2") here shows error
              

                Next i
End Sub

 

[BobBridges]

You say you get an error message, but what does the message say?

 

[Marc L]

Hi,​

​

just a bad use / misunderstanding of what is a variable object !​

For example as recon is a variable object on a workbook so it can not be used like Workbooks(recon) as recon is not a String variable,​

and it can be easily achieved without any workbook variable …​

​

According to forum rules edit your initial post and add code tags via the icon in the 3 dots menu.​

​

 

[aleksandra123]

It is error 13 type mismatch. You are right this is problem with variables but now I dont know how to handle that as the name of sheets and workbooks should be taken from the file as they are typed there

 

[aleksandra123]

Hi
I have rebuilt the macro however there is something wrong with the last part when it comes to copy and past data.

Sub CopyData()

  Dim i As Integer
    Dim FieldAVal As String
    Dim FieldBVal As String
    Dim FieldCVal As String
    Dim FieldDVal As String
    Dim wbk1 As Workbook
    Dim wbk2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Iter As Integer
    Dim recon As Workbook
    Dim FilePath As String
  
    
  

FilePath = ThisWorkbook.Worksheets("Macro").Cells(11, 1)


Set recon = Workbooks(FilePath)



Workbooks.Open Range("A10").Value
 recon.Activate
    
 
    Iter = Cells(1, 3).Value
  
    
                    
             For i = 1 To Iter
                FieldAVal = Cells(i + 14, 2).Value
                FieldBVal = Cells(i + 15, 3).Value
                FieldCVal = Cells(i + 16, 4).Value
                FieldDVal = Cells(i + 17, 5).Value
                
 Set wbk1 = Workbooks(FieldDVal)
   Set wbk2 = Workbooks(FieldCVal)
    Set ws1 = wbk1.Sheets(FieldBVal)
  Set ws2 = wbk2.Sheets(FieldAVal)

                
Workbooks(wbk1).Worksheets(ws1).Range("A1:V1000").Copy Workbooks(wbk2).Worksheets(ws2).Range("B2")
                

                Next i
End Sub

 

[BobBridges]

"Something wrong"? Again, be specific. What's wrong? Do you get an error message, or what? If so, what (exactly) is the message? If all you tell us is "something wrong", about the only helpful comment we can make is "you probably made a mistake".

 

[aleksandra123]

Hi
For this part Run Time error 13:
Workbooks(wbk).Worksheets(FieldBVal).Range("A1:V1000").Copy Workbooks(recon).Worksheets(FieldAVal).Range("B2")

 

[BobBridges]

I don't know what 13 is. What does the text of the message say?

 

[vletm]

aleksandra123
Which one code line do You use?
Workbooks(wbk1).Worksheets(ws1).Range("A1:V1000").Copy Workbooks(wbk2).Worksheets(ws2).Range("B2")
Workbooks(wbk).Worksheets(FieldBVal).Range("A1:V1000").Copy Workbooks(recon).Worksheets(FieldAVal).Range("B2")
a) ... with wbk1, ws1, wb2 and ws2 ?
b) ... with wbk, FieldBVal, recon and FieldAVal ?

After that, have You verified eg with Watch-option - what are those values?

Do You have there merged cells?