Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

kitten emergency: Jan 13, 2008; get meow this wack-ass crystal prison

I've got data set up like so:

code:

Dept.   Phone Number   Other Stuff
100     5555555555     Name
200     5555555551     Name
300     5555555552     Name
300     5555555553     Name
400     5555555554     Name

And so forth. What I'm trying to do is coax a macro into copying each row into a pre-existing set of sheets based on the first column. (ie, workbook already created with a sheet for each dept)

Would there be a way to do this with one master macro, rather than having a macro for every department? I'm not really familiar with VB, but any sort of guidance would be appreciated.

# ¿ Oct 9, 2010 22:01

Adbot: ADBOT LOVES YOU

# ¿ May 6, 2024 07:58

kitten emergency: Jan 13, 2008; get meow this wack-ass crystal prison

gwar3k1 posted:

Not the most efficient way, but it will do what you've asked:

It was failing, but I see what the problem is. This should work okay, thanks!

Ok, no, it's still failing. It's giving 'subscript out of range' after a few rows, and it's skipping the first two sheets. I thought that maybe it was checking the object name instead of what the sheet was named, but it doesn't seem to be the case.

kitten emergency fucked around with this message at 23:13 on Oct 9, 2010

# ¿ Oct 9, 2010 23:04

kitten emergency: Jan 13, 2008; get meow this wack-ass crystal prison

gwar3k1 posted:

I've made the assumption that your department sheets are named exactly the same as the department code. Also, I have made the assumption that there is a sheet for every department on your master list sheet.

I bet its saying subscript out of range because a sheet name doesn't match / the intended sheet doesn't exist.

Can you provide an example of department code and sheet name, and say which line is causing the exception (highlighted yellow ehen you click debug).

code:

4	5555555555	JSIN 
5	5555555555	SERGEY
6	5555555555	SERGEY B
20	5555555555	PILLERIN
30	5555555555	PLIKSA 
54	5555555555	VLADIMIR
54	5555555555	MAKSYM 
100	5555555555	ANGELA 
100	5555555555	ALRIC E
100	5555555555	LOANER 
100	5555555555	PAT

Error occurs on this line, once it hits the sixth row (54)

code:

 Sheets(sDept).Select

I do have sheets for every value in the first column, including 54. Additionally, it's skipping the first two sheets I have in the book (For instance, I have sheets named 4, 5, 6, 20, 30. It puts '6' under sheet '30'. The records for 20 and 30 never even appear, errors out as soon as it hits row 6 (code 54))

ed: the sheets are set up as follows

code:

(Main) (4) (5) (6) (20) (30) (54) (100) (200) (300) (etc)

# ¿ Oct 9, 2010 23:39

kitten emergency: Jan 13, 2008; get meow this wack-ass crystal prison

gwar3k1 posted:

It needs to be run from the main sheet. Here's some ammended code:

Works brilliant! Thank you!

edit: would you care for a plat upgrade? let me know - username at gmail dot com.

kitten emergency fucked around with this message at 00:17 on Oct 10, 2010

# ¿ Oct 10, 2010 00:11

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread