I want a Macro to do my work

[Parra]

This is very tedious so I would like a macro to do the following.

I have the following data and I need it to be inserted in its related worksheet in a particular cell.

Column A, Column B
030, 2500
031, 3600
032, 4500
040, 1500
045, 8900
etc., etc

Column A is the name of the worksheet where I want the data in Column B inserted.

example. 030, 2500 I would go to worksheet 030 and in cell F12 enter 2500.

I know there is a way to automate this, but I don't know how, can someone help me.

Thanks

[Al Chara]

One question first: Do those commas actually appear in your data? If they do then get rid of them. Try the following code:

Dim rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns("A"))
For Each cell In rng
Worksheets(cell.Value).Range("F12").Value = ActiveSheet.Range("B" & cell.Row)
Next

Edit as needed.

[GaryB]

I think this does what you want. Give it a go and let me know:

Sub Macro1()


myRow = 1

Do Until Cells(myrow, 1) = ""
myName = Cells(myrow, 1)
Sheets("sheet" & myName).Cells(12, 6) = Cells(myRow, 2)
myRow = myRow + 1
Loop

'
End Sub



HTH

GaryB

[Parra]

Hey guys thanks for the help, I tried both macros and I got the following error message for both.

Run Time Error '9'
Subscript out of range.

Please advice. Thanks

[Ricky Morris]

Try Gary B's macro but modify the follwing line to excelude "sheet" & from:

Sheets("sheet" & myName).Cells(12, 6) = Cells(myRow, 2)

to:

Sheets(myName).Cells(12, 6) = Cells(myRow, 2)

[Parra]

Hi Ricky, I tried your suggestion and I got the same error. Do you have any ideas why? I can't figure it out.

My vb knowledge is limited and I don't really understand the code. I know what it can do and is capable of.

Thanks

[Ricky Morris]

The following code just worked for me.

Sub Macro1()
myRow = 1
Do Until Cells(myRow, 1) = ""
myName = Cells(myRow, 1)
Sheets(myName).Cells(12, 6) = Cells(myRow, 2)
myRow = myRow + 1
Loop
End Sub

[Parra]

Hi Ricky I got the same message???? I don't know why??

I have the same data as I do in the first message and then I have the data in "Sheet 1" and then from left to right I have sheets 30, 31, 33, 40 and 45. This is actually a summary of the 85 sheets there really are, I am doing this on a test work book.

Any suggestions? Or info I can provide?

Thanks

[Cosmos75]

See below for info in error message...

http://www.mrexcel.com/board/viewtop...c=4841&forum=2

[Cosmos75]

Al Chara,

What does Intersect do?