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

 

[Ricky Morris]

Make sure you sheet name are exactly as it appears on the list. Your list has the Sheet names with 0 in front but your sheet names don't.

"30" is not the same as "030"

 

[Al Chara]

Cosmos,

From the help file on INTERSECT:

Returns a Range object that represents the rectangular intersection of two or more ranges.

Syntax

expression.Intersect(Arg1, Arg2, ...)

expression Optional. An expression that returns an Application object.

Arg1, Arg2, ... Required Range. The intersecting ranges. At least two Range objects must be specified.

 

[Parra]

OK, I kept getting the error message but I looked at the sheets and the data did transfer to 3 of the sheets, but I don't know which Macro it was because I tried all the ones that were posted. I tried them again and none of them worked???

 

[GaryB]

Ok, I have no idea why this should work, but it did when I tried it! (This assumes your worksheets are called 30,31.. and not 030, 031 ...)

change the line
Sheets(myName).Cells(12, 6) = Cells(myRow, 2)
to
Sheets("" & myName).Cells(12, 6) = Cells(myRow, 2)

The only thing I can think of is that it didn't like the sheet names being numbers and this forced it to look at them as text .. maybe.. sort of...

Hopefully this will do it for you!

GaryB
This message was edited by garyb on 2002-04-16 09:04

 

[Parra]

Hey Gary, Thanks for the help. The last post you put up did the trick.

Thank you.

Parra

 

[GaryB]

Glad to have helped. (Always nice when someone says thanks as well !)
Cheers!

GaryB