Quick Excel Help Page 2

  • JetSetWilly 27 Jun 2007 17:13:14 5,720 posts
    Seen 1 year ago
    Registered 16 years ago
    Is their a property or method on the Sheets object that returns how many sheets there are currently?

    [code]Sheets("BLANK").Copy After:=Sheets.Count[/code]

    More here Razz:

    http://support.microsoft.com/kb/288402

    Edited by JetSetWilly at 18:08:25 27-06-2007
  • MrWorf 27 Jun 2007 21:12:27 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    There isn't one. My code is very basic. :)

    Aww cheers that works a treat. :))

    Hit a slight snag though. I've coded it so the new copied worksheet is automatically named after the cell contents of Cell D2. But I'm worried if someone forgets to put that information in before submiting the form because if D2 is blank it breaks the code.

    I've experimented with various ways of using IF/ELSE codes to fix this but to no avail.

    Basically it's:
    [code]Sub Macros ()

    Sheets("BLANK").Select
    IF D2 = "" THEN BREAK
    ELSE NEXT
    END IF
    Sheets("BLANK").Copy After:=Sheets.Count
    Sheets("BLANK (2)").Select
    Sheets("BLANK (2)").Name = Worksheets("BLANK (2)").Range("D2").Value[/code]

    But I'm struggling to put this in a way that VB will recognize. I've just sort guessed what it might be.
  • MrWorf 27 Jun 2007 21:35:55 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Figured it out!

    :)

    [code]
    Sheets("BLANK").Select
    If ActiveSheet.Range("D2").Value = "" Then
    MsgBox ("Please enter Risk Assessment Number")
    End
    End If
    Sheets("BLANK").Copy After:=Sheets(Sheets.Count)
    Sheets("BLANK (2)").Select
    Sheets("BLANK (2)").Name = Worksheets("BLANK (2)").Range("D2").Value
    [/code]
  • MrWorf 21 Sep 2007 15:04:52 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    *BUMP*
  • MrWorf 21 Sep 2007 15:04:53 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Argh! Anyone know if it's possible to paste the contents of a colum into a row? So instead of the data moving down the sheet I want to cut and paste it so it goes along the sheer.. possible wihtout cut and pasting every cell?
  • jellyhead 21 Sep 2007 15:06:00 24,351 posts
    Seen 4 days ago
    Registered 18 years ago
    Razz wrote:
    Argh! Anyone know if it's possible to paste the contents of a colum into a row? So instead of the data moving down the sheet I want to cut and paste it so it goes along the sheer.. possible wihtout cut and pasting every cell?
    Does "Paste Special" allow that?

    yay it does, Transpose is the kiddy apparently.
  • MrWorf 21 Sep 2007 15:11:38 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    \o/

    Thanks mate! :) :) :) :)
  • MrWorf 21 Sep 2007 15:11:38 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    *bump*
  • MrWorf 26 Sep 2007 13:08:40 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    *bump*
  • MrWorf 26 Sep 2007 13:08:41 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Please help me again!

    I'm trying to write an IF statment for the following

    =IF(B2=E2 or C2=E2 or D2=E2,TRUE,FALSE)

    This doesn't work! :( I just get NAME? :(

    Basically if c1 matches either of the values in a1, b1 or c1 I want e1 to display true or false. I know this is possible but perhaps I'm going around it in the wrong way?

    Please help! \o/
  • tannerd 26 Sep 2007 13:12:25 2,691 posts
    Seen 3 days ago
    Registered 18 years ago
    Try:

    =IF(OR(a1=d1, b1=d1, c1=d1),true,false)
  • MrWorf 26 Sep 2007 13:17:28 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    \o/

    Cheers!
  • MrWorf 26 Sep 2007 13:17:29 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Works a treat :)
  • DamoVotf 26 Sep 2007 14:18:16 1,465 posts
    Seen 1 year ago
    Registered 15 years ago
    Micro_Explosion wrote:
    /secretly has 80+meg excel files
    /and files with calculations that takes 5 minutes each time

    Noob, strokes 1gig excel file with 36 hr recalculation time.

    Needs a super computer to open.
  • gang_of_bitches 19 Nov 2007 16:11:59 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    OK, thought I was being terribly clever, that as it turned out was an error.

    So I've got a very simple spreadsheet of sales by date running horizontally against the top against different lines runnuing vertically. Thing is I'm running out of room and now want to swap the two around. No problem I thought, I'll just do copy-paste special-transpose. But the problem is its not having it, says fields aren't the same size and shape. How do I get round this bollocks?
  • Lutz 19 Nov 2007 16:15:46 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.
  • gang_of_bitches 19 Nov 2007 16:17:28 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Lutz wrote:
    IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.

    Arse, I was under the impression that was exactly what it was supposed to do. What the hell does it do then?
  • gang_of_bitches 19 Nov 2007 16:17:45 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Lutz wrote:
    IIRC you can't transpose rows with columns. I wanted to do the same years ago and couldn't.

    Arse, I was under the impression that was exactly what it was supposed to do. What the hell does it do then?

    Oh and thanks.
  • ming 19 Nov 2007 16:19:13 275 posts
    Seen 7 years ago
    Registered 15 years ago
    If you have more then 256 rows then it wont work as Lutz said. However this error is normally because you have one or more merged cells and the paste area is not the same. Just unmerge the cells and fix the formatting afterwards.
  • Lutz 19 Nov 2007 16:19:41 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    I dunno, it's not even in my Paste Special options.
    Which excel you using?
  • Lutz 19 Nov 2007 16:21:15 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Oh shit! YEah you can.

    But you can't do it with a whle row or a column in one go.
    hang on.

    Edit: Right. Data in A1:A4. If you select copy on A1:A4 you can't paste into A1, B1, C1, D1, because A1 is in both copy and paste. You could paste into B1, C1, D1, E1 though.

    And due to that by default you can't do a whole row or column in one go.

    You may be able to do a row into a column actually (256 lines into 65536 should go)
  • gang_of_bitches 19 Nov 2007 16:21:31 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Some old shit one, possibly 98, certainly pre-xp prettiness revamp.

    For me it's the bottom right tick-box in paste special.
  • gang_of_bitches 19 Nov 2007 16:27:36 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Lutz wrote:
    Oh shit! YEah you can.

    But you can't do it with a whle row or a column in one go.
    hang on.

    Edit: Right. Data in A1:A4. If you select copy on A1:A4 you can't paste into A1, B1, C1, D1, because A1 is in both copy and paste. You could paste into B1, C1, D1, E1 though.

    And due to that by default you can't do a whole row or column in one go.

    You may be able to do a row into a column actually (256 lines into 65536 should go)

    Ah that kinda makes sense, its only pretty small at the moment and I was only trying to switch cells rather than full rows and columns. Oh well ho hum, I'll just hide cells as info gets old.
  • MrWorf 22 Nov 2007 15:26:01 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Aw crap! My calculations aren' automatically updating! ;_;

    /double clicks cells for if statements to update

    I know there must be a setting somewhere that forces Excel to update all calculations automatically... is there?

    Please halp! (._.)

    Edit: Auto not manual! :D
  • JetSetWilly 22 Nov 2007 15:27:59 5,720 posts
    Seen 1 year ago
    Registered 16 years ago
    Tools->Options->Calculation->Select Automatic
  • Lutz 22 Nov 2007 15:28:39 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    F9
  • MrWorf 22 Nov 2007 15:28:40 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    \o/

    /kisses JSW in the face!
  • Lutz 22 Nov 2007 15:29:07 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    :(

    /wants a sloppy kiss too

    NO TONGUES.
  • MrWorf 22 Nov 2007 15:30:13 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    /puts Lutz on shoulders

    Oy, spread your arms and close you eyes

    /runs around making plane noises
  • JetSetWilly 22 Nov 2007 15:31:19 5,720 posts
    Seen 1 year ago
    Registered 16 years ago
    Razz wrote:
    \o/

    /kisses JSW in the face!

    Aaww shucks

    /blushes
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.