read cell value on master, copy to matching sheet.

**ABBOV** · 08-10-2011, 06:27 AM

Hello,

I've got the following problem, I've got a sheet called "Invoer", here i've got 11 rows with data.
(B4 till O14) where the values in column B match 11 other sheets.

I'm trying to copy every row to its matching sheet. like this:

Sub wegschrijven()

If Blad1.Range("B4") = "Internet" Then
    Blad1.Range("B4:N4").Copy
    Blad2.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial
End If

If Blad1.Range("B4") = "Mail" Then
    Blad1.Range("B4:N4").Copy
    Blad3.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial
End If

etc
etc
etc

End Sub

But this will give me a very long macro, because the sorting and quantities can vary.
I.ve attached an example so you can see what i mean.

Is there a quicker way?

Thanks in advance.

ABBOV

**snb** · 08-10-2011, 06:50 AM

Sub snb()
  sn = Sheets("Invoer").Cells(3, 2).CurrentRegion.Resize(, 14)
  For j = 2 To UBound(sn)
    Sheets(sn(j, 1)).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1).Resize(, 14) = Application.Index(sn, j)
  Next
End Sub

PS. Do not use merged cells, VBA doesn't treat them correctly
The setup of the 'invoer' sheet can be improved, so less code is required (structure precedes coding).