Copy from one workbook to another using loop

Question

I have developed the below code however it shows me the error here Runtime error 9 and 13. When it comes to part that the macro should copy data from one workbook to another. I know that I wrongly assigned the variables but no clue how to change it.

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?

Code:

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

Variatus Variatus · Accepted Answer · 2020-07-10T07:37:41

Set your logic before you start writing code. Start by writing Option Explicit at the top of your blank code module.

It seems, you have a workbook called like "Reconciliation". It seems that you want to call this workbook Wb. Therefore your first line of code should be

Dim Wb As Workbook ' the reconciliation workbook
It appears that somewhere in that workbook there are cells A9 and A11. Where? On a worksheet. Which worksheet? That leads you to the second line of code.

Dim Ws As Worksheet ' the worksheet from which to gather company info
Continue like that until you have identified each part of your project by its nature (workbook, worksheet, string, number), by its function in your project (supplier of data, receiver of data, helper), and given it a name.

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

creates two workbook objects. You haven't declared them and give no indication of their function in your project. But it's clear that your code will fail if the ranges A9 and A11 don't hold the names of open workbooks. They must be open because your code doesn't open them, even if the cells hold full file names with their respective paths.

Observe that both A9 and A11 are on the ActiveSheet. That is so because you don't specify any sheet in particular. The ActiveSheet will be any sheet in the object Wb with a name like "Reconciliation" that happens to be active at the time - a very vague description. Chances that the correct sheet will be found are quite slim.

All of this confusion is due to the lack of planning before you started to write code. Go back and start over. Think in much smaller steps than you have done until now. However, one step that you don't have to think is what to Select or Activate. The answer is uniformly "Nothing". Wb.Worksheets("MySheet 1").Cells(9, "A") is a very clear address. VBA can find it. It can obtain its Value, its RowHeight, its Formula and change any of these and more just as soon as it can Select or Activate it. Activating and selecting is an action the user needs. VBA or Excel don't.

And, before I forget, VBA addresses range by name and cells by their coordinates. Range("A9") is a work-around to use a synthetic name for a range which is a single cell. Nothing good will ever come of such acrobatics. Since you already mastered the syntax for addressing cells, stick with it for that purpose. Use names to address ranges of several cells but bear in mind that names like "A1:C7" are artificially constructed from cell coordinates. It's a great system but, alas, the lowest rung on that particular ladder. You can do much more with real names that you create and manage yourself.

Copy from one workbook to another using loop

2 Answers