How to add data to separate worksheets?

S

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
 
N

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
'<<=============
 
N

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top