How to add data to separate worksheets?

[Special-K]

I'm a novice at VBA so I need a bit of help.
I have a worksheet (sheet 1) like this

Col A Col B
Sheet2 ABC
Sheet3 DEF
Sheet3 GHI
Sheet4 JKL

I want to add each row of this spreadsheet to the next blank row of the
worksheet specified in column A.
So if the worksheets are like this

Sheet 2 Sheet 3 Sheet 4
Col A Col A Col A
A C E
B CD

I want to end up like this:

Sheet 2 Sheet 3 Sheet 4
Col A Col A Col A
A C E
B CD JKL
ABC DEF
GHI

Also each time I run the macro the number of rows in Sheet 1 will vary
so I need to loop through sheet 1 until Col A is blank.

Can anyone show me how to do this?
Thanks

 

[Norman Jones]

Hi Special-K,

 

[Norman Jones]

Hi Special-K.

Try something like:

'=============>>
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim destSH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim destRng As Range

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE

Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

Set rng = SH.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)

For Each rCell In rng.Cells
With rCell
On Error Resume Next
Set destSH = Sheets(rCell.Value)
On Error GoTo 0

If Not destSH Is Nothing Then
Set destRng = _
destSH.Cells(Rows.Count, "A").End(xlUp)(2)
destRng.Value = .Offset(0, 1).Value
End If
End With
Next rCell
End Sub
'<<=============

 

[Norman Jones]

Hi Special-K,

Change:

Set rng = SH.Range("A1:A" & _
Cells(Rows.Count, "A").End(xlUp).Row)

to

Set rng = SH.Range("A1:A" & _
SH.Cells(Rows.Count, "A").End(xlUp).Row)

 

[Special-K]

That's brilliant! Worked a treat!
Thanks Norma

 

[Special-K]

That's brilliant! Worked a treat!
Thanks Norma