Macro to take a range of names and paste each on separate sheets

[squeakums]

I need a macro that will take names from range A21:A2 and paste on C3:F3 (merged cell) on another sheet.

For example:

Column A

Bird, Tweety
Bird, Big
Man, Goofy

It will take Bird, Tweety and place it on cell C3:F3 on a different sheet which we'll call "Bird, Tweety" for now. The sheets are in the same order as the range starting from the bottom. Help?

 

[venkat1926]

I hope you have already have sheets called "bird, tweety" etc (correct spellling-better copy A2 on sheet tabl etc than typing)
the main sheet is sheet 1 where the values "bird, tweety" etc on in A2 down without any blank)
in that case try this macro. give feedback

Sub test()
Dim rng As Range, c As Range, x As String
With Worksheets("sheet1")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))
For Each c In rng
x = c.Value
c.Copy
With Worksheets(x)
.Range("c3").PasteSpecial
End With
Next
End With
End Sub

the sheets may be in any order.

 

[squeakums]

I tried this but it keeps erroring out on: With Worksheets(x)

Is that supposed to remain the name of With Worksheets(x) or is the (x) supposed to be changed to something else? I presume that it is supposed to stay the same, but it isn't working. Any ideas? Thanks for your help.

Sub Pull_Test()
'
' Macro1 Macro
' Macro recorded 3/21/2009 by test
'

'
Windows("Test's Team (By Group).xls").Activate
Sheets("Monthly Logged Details").Select
Dim rng As Range, c As Range, x As String
With Worksheets("Monthly Logged Details")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))
For Each c In rng
x = c.Value
c.Copy
Windows("Test's Team (Individual Monthly).xls").Activate
With Worksheets(x)
Range("c3").PasteSpecial
End With
Next
End With
End Sub

 

[venkat1926]

what is the error message.

have you opend the sheets called "birds,tweety" etc. -correct and exact spelling as in the cells of sheet1

 

[squeakums]

The error message that I am getting is a debug error with the code:

With Worksheets(x)

I am pulling from two different workbooks. The first workbook is called "test's team (by group)" and the second workbook is called ("test's team (individual monthly)".

I want to pull from "test's team (by group)" on sheet ("monthly logged details") range going up from A21 to A2 - and on workbook ("test's team (individual monthly") I want the macro to place each name on each sheet on merged cell C3:F3.

The With Worksheets(x) isn't working for some reason. The macro is the following so far:

Sub Pull_Wendy()
'
' Macro1 Macro
' Macro recorded 3/21/2009 by Wisconsin Division
'

'
Windows("Test's Team (By Group).xls").Activate
Sheets("Monthly Logged Details").Select
Dim rng As Range, c As Range, x As String
With Worksheets("Monthly Logged Details")
Set rng = Range(.Range("a21"), .Range("a2").End(xlDown))
For Each c In rng
x = c.Value
c.Copy
Windows("Test's Team (Individual Monthly).xls").Activate
With Worksheets(x)
Range("c3").PasteSpecial
End With
Next
End With

 

[venkat1926]

1. in your first message there appears to be no indication that the sheets "birds,tweeky" etc are in different workbooks.

2. you have not confirmed whether you have opened the new sheets "brids,tweaky" etc in the second workbook

you have said a21 to a2 I hope you mean a2 to a21 and a21 is the last cell in column A.

I have mdoified the macro little and see whether it wokrs.

With Workbooks("Test's Team (By Group).xls").Sheets("Monthly Logged Details")
Dim rng As Range, c As Range, x As String
'With Worksheets("Monthly Logged Details")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))
For Each c In rng
x = c.Value
c.Copy
With Workbooks("Test's Team (Individual Monthly).xls").Worksheets(x)
.Range("c3").PasteSpecial
End With
Next
End With
End Sub

 

[squeakums]

Yes that works. Thanks again for all of your help.