How to add data to separate worksheets?
 

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


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=573666

How to add data to separate worksheets?
 

Hi Special-K,



--
---
Regards,
Norman



"Special-K" wrote
in message ...

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


--
Special-K


------------------------------------------------------------------------
Special-K's Profile:
http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=573666

How to add data to separate worksheets?
 

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


---
Regards,
Norman


"Special-K" wrote
in message ...

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


--
Special-K


------------------------------------------------------------------------
Special-K's Profile:
http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=573666

How to add data to separate worksheets?
 

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)


---
Regards,
Norman

How to add data to separate worksheets?
 

That's brilliant! Worked a treat!
Thanks Norma

--
Special-

-----------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...nfo&userid=747
View this thread: http://www.excelforum.com/showthread.php?threadid=57366

How to add data to separate worksheets?
 

That's brilliant! Worked a treat!
Thanks Norma

--
Special-

-----------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...nfo&userid=747
View this thread: http://www.excelforum.com/showthread.php?threadid=57366