VBA Error 400 when trying to copy/paste formulas and format in a loop

Question

I am trying to copy and paste the format and formulas of a "masterrow" to a range in a loop. The loop also fetches data from another workbook, which therefore is the ActiveWorkbook during the loop.

I am thinking this "ActiveWorkbook" issue is what causes the problem, but I need help for the solution. I have supplied the loop function below. I hope you can help me.

Sub Worksheet_UpdateAllItemCostData()

Dim material As Variant
Dim fndEntry As Range
Dim wb1 As Workbook, wb2 As Workbook
Dim lr As Long, I As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb1 = ActiveWorkbook

lr = wb1.Sheets("Sagsnr.").Cells(Rows.Count, "C").End(xlUp).Row

If lr < 21 Then
    Exit Sub
End If

Workbooks.Open Filename:="G:\Backoffice\Tilbudsteam\Kostdatabase\Matcost.xls", ReadOnly:=True

Set wb2 = ActiveWorkbook

    For I = 21 To lr

            wb1.Sheets("Sagsnr.").Rows("1:1").Select
            selection.Copy
            wb1.Sheets("Sagsnr.").Rows(I).Select
            selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=True, Transpose:=False
            selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            selection.EntireRow.Hidden = False

    material = wb1.Sheets("Sagsnr.").Range("C" & I).Value

    Set fndEntry = wb2.Sheets("Matcost").Range("D:D").Find(What:=material)

    If Not fndEntry Is Nothing Then

            wb1.Sheets("Sagsnr.").Range("B" & I).Value = wb2.Sheets("Matcost").Range("H" & fndEntry.Row).Value  'Product group
            wb1.Sheets("Sagsnr.").Range("E" & I).Value = wb2.Sheets("Matcost").Range("Q" & fndEntry.Row).Value   'Available Stock

        End If

    Set fndEntry = wb2.Sheets("Matcost").Range("C:C").Find(What:=material)

        If Not fndEntry Is Nothing Then

            wb1.Sheets("Sagsnr.").Range("B" & I).Value = wb2.Sheets("Matcost").Range("H" & fndEntry.Row).Value  'Product group
            wb1.Sheets("Sagsnr.").Range("E" & I).Value = wb2.Sheets("Matcost").Range("Q" & fndEntry.Row).Value   'Available Stock

        End If
Next I

wb2.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Maybe you could try to remove all `Select` and `Selection`. [Look here for HowTo](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Vincent G, Jul 19 '16 at 08:52
Have you tried running through the code line by line to see exactly where the error occurs? — Clusks, Jul 19 '16 at 09:17
Thank you, I will look through your reference topic Vincent G, but if you also could see the solution, it would be nice to know. I am still in the learning phase. @Clusks - I have, it works fine if I skip the entire loop where I activate wb2, so this leads me to the conclusion that it is an ActiveWorkbook issue, but I just cannot get my head around it. — Smeltet, Jul 19 '16 at 09:28

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

As you said it is probably an ActiveWorkbook issue when you activate your wb2, you should try this:

Set wb2 = Workbooks.Open (Filename:="G:\Backoffice\Tilbudsteam\Kostdatabase\Matcost.xls", ReadOnly:=True)

Instead of

Workbooks.Open Filename:="G:\Backoffice\Tilbudsteam\Kostdatabase\Matcost.xls", ReadOnly:=True

Set wb2 = ActiveWorkbook

Also, if your code is in wb1 you can avoid the ActiveWorkbook and do:

Set wb1 = ThisWorkbook

As an additional advice, you should try to avoid .Select in your code How to avoid Select in Excel VBA macros

edited May 23 '17 at 11:58

Community

1
1

answered Jul 19 '16 at 09:54

Rémi

372
3
8

Thank you for your proposal, but it still throws an error, which I think is related to the fact that whether or not I set wb2 to ActiveWorkbook or not, it is working in wb2 somehow. The error is thrown the minute it reaches the selection.copy line – Smeltet Jul 19 '16 at 10:07
1

And what happens if you get rid of the `.Select` and `Selection.` ? Write `wb1.Sheets("Sagsnr.").Rows("1:1").Copy` same for the paste – Rémi Jul 19 '16 at 10:49
Thanks so much, sometimes it is as simple and right in front of me. – Smeltet Jul 19 '16 at 12:02

VBA Error 400 when trying to copy/paste formulas and format in a loop

1 Answers1