1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Copy Certain Cells from a Row to a certain Sheet..

Discussion in 'Business Applications' started by Galstar, Feb 14, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    Okay I have one main worksheet named "A" I insert rows into this sheet and Depending on what is is Col B I want info copied to another Sheet based upon what is put into Col B.

    Col B can contain "Army", "Navy","Marines"

    If I enter Army into Col B, then Copy that selected Row, Col: A, B, C, E,J into worksheet Named "Army" into first unused Row in Col A,B,C,D,E.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, that is more straightforward.

    Make sure you have labels in row 1 of the "other" sheets. Then try something like the code in the attached (which can be run with the shortcut CTRL+Shift+M).

    HTH
     

    Attached Files:

  3. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    Okay sorry, col B itself is used as the guide to what sheet it need to go to, but isn't copied itself. I.e Army is put into Col B, But only say Col A, C, D,F,G are copied itself to A,B,D,G,J etc.... Sorry I didn't think about that the first time. Second question, which may be getting out of excel, can it actually look to see if the Col C on Sheet1(which will be lastname,first name) is in said sheet(i.e Army) and not copy it if it is on there?

    Also I see no labels in your example of your attached files. So how does that come into play?

    Also the formatting, how do I force the formatting? I.e If I have col A on Sheet Army set bold, using your code, it copies the data but turns it back to normal text. On Sheet1 it is Bold as well.. Using a straight copy as you suggested, doesn't that just wipe out formatting, color's, and/or formulas as well.. How do you get around this?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    For the 2nd question, the short answer is "Yes". But let's break it down. :)

    See the Check macro in the attached. Firstly there are 3 lines wrapping the bulk of the code (On Error GoTo 100 -- 100: -- Exit Sub). They're there to trap user error (e.g. running the code when a blank row is selected).

    The first line in the bulk of the code is:

    Res = Application.Match(Cells(ActiveCell.Row, 3), Sheets(Cells(ActiveCell.Row, 2).Value).Range("B:B"), 0)

    This uses the MATCH function but VBA-style. It's in 3 parts:

    1. Cells(ActiveCell.Row, 3) = the value in column 3 of whichever row is active. This is what to look for (i.e. Name)

    2. Sheets(Cells(ActiveCell.Row, 2).Value).Range("B:B") = where to look for it.
    Sheets(Cells(ActiveCell.Row, 2).Value) figures which sheet to check from the value in column 2 of whichever row is active (e.g. "Army") ; Range("B:B") is which column to check on said sheet.

    3. The 0 at the end forces an exact match.

    The MATCH has to be done "virtually", "in memory", call it what you will because ... if you try & do it physically (directly on a sheet) & there's no match it'll crap out. A side issue you should consider is whether Name should be used to match, because you may well have (e.g.) 2 "John Smith"s. You really ought to attempt match on something that will be unique (SS#?) if possible.

    The rest of the (bulk of the) code -- If ... to End If -- is just what to do with the match result. Run the macro (CTRL+Shift+M) with a cell in row 2 of Sheet1 selected. You'll get a prompt Already listed because Bush, George exists in column B on the Army sheet. Then run it again w/a cell in row 3 of Sheet1 selected. You'll get a prompt Not listed because Ahab, Captain doesn't exist in column B on the Navy sheet.

    Finally, you're wrong about the formatting. Select Navy!A2 & you'll see from the Formatting toolbar that that cell isn't bold. Then switch to Sheet1, select A3 & run the List macro. This will copy the selection to Navy!A2 including the formatting.

    HTH -- post back when you're ready for more steps. ;)
     

    Attached Files:

  5. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    Ready For more. I follow the code quite well, great explaination. So takin git to the next step would be to copy the copying code into this, so if no match then copy data etc.
    So then we are left with part one of the question as certain cols to be copied to certain col. I think I have your first example firgured out as to changing the offsets etc. But still can't figure out if on Sheet1 Col B says army, copy data to the Army Sheet but don't copy the Col B itself. i.e use the col B as a guide but not to copy itself.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    What you can do now is expand the List code and incorporate it in the (bulk of the) Check code.

    1. Edit the Check code (bulk) to:

    If IsError(Res) Then
    List
    Else
    MsgBox "Already listed."
    End If


    (which in English means -- if no match on name check, run List ; otherwise just display a prompt to alert user of name check outcome)

    2. Replace the List code w/this:

    Sub List()
    Cells(ActiveCell.Row, 1).Copy _
    Sheets(Cells(ActiveCell.Row, 2).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

    Cells(ActiveCell.Row, 3).Copy _
    Sheets(Cells(ActiveCell.Row, 2).Value).Range("A" & Rows.Count).End(xlUp).Offset(0, 1)
    End Sub


    Remember, List is what runs if name check is negative. So:

    Cells(ActiveCell.Row, 1).Copy = copy what's in column A (,1) of active row:

    and:

    Sheets(Cells(ActiveCell.Row, 2).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = where to copy it to. Sheets(Cells(ActiveCell.Row, 2).Value) gives you sheet to copy to (ActiveCell.Row, 2 picks up from col B of active row) & Range("A" & Rows.Count).End(xlUp).Offset(0, 1) gives you first free row in col A of sheet to copy to.

    BUT: notice that when the copy runs again there's a different syntax -- Range("A" & Rows.Count).End(xlUp).Offset(0, 1). This is so it can get it's bearings from the first value copied (from the "record" to be xferred). So what you should make sure is that the first item (AKA "field") copied from the record is something that will always be present in all records. If you follow. (poor explanation, I know :( )

    EDIT: going offline now. Latest MS Sec updates are screwing my AVG & ZoneAlarm, so I have to install update 1, shut down, restart, check, install u2, etc. Check back in a coupla hrs. :)
     
  7. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    okay say the col we are using has spaces in it. The layout is as follows(dictacted from above me)
    LastName,FirstName (spaces) SSN... Not always the same spaces becuase of length of name, and the Cell has word wrapping on it.
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Now you've truly lost me. Post a small sample file w/some made-up names to clarify the layout.
     
  9. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    what I was saying is that col C which is what were are using as the match has Lastname,FirstName (spaces) SSN. Col C has word wrapping on Sheet1 becuase of screen size. That if you do a match it's not finding them on the say Army sheet becuase the same col on Army sheet has same format, but is smaller font, and the spaces between firstname and ssn can be different. So what I am asking is can you do the match but trim out all spaces so you can check for exact match correctly?

    Also second question. We are using Col B on sheet1 to detrimine what sheet to copy to. I.e Army. However I don't want to copy data in Col B itself. I don't need the word army on the Sheet Called Army. So how do I use Col B so we know what sheet, but copy itself?
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Re: "trim out all spaces" -- the TRIM function can be used in VBA, but that wouldn't really work if the spaces were in the values in the lookup range. But the thing that really concerns me is "spaces between firstname and ssn" -- that sounds like you have data combined in cells which ought to be separate. So, it really would be best to post a small sample file w/some made-up names to clarify the layout -- as I said previously.
     
  11. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    Okay I have attached the form. As you can see when you do a Ctrl-Shift-M it copies the record to the right tab, however it copies the format as well. The tabs behind the main tab have a different format, becuase the data is less. I want to format the say Navy tab once the record is copied over? Should that be possible to define via code the format, or even copy the format from previous record onto on new record just put in?
     

    Attached Files:

  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi again. ;)

    To copy the format from row 3 (of the relevant sheet) to the bottom row (i.e. most recently added) (of the relevant sheet):

    Sheets(Cells(ActiveCell.Row, 2).Value).Rows(3).Copy

    x = Sheets(Cells(ActiveCell.Row, 2).Value).Range("A" & Rows.Count).End(xlUp).Row

    Sheets(Cells(ActiveCell.Row, 2).Value).Rows(x).PasteSpecial Paste:=xlFormats, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
     
  13. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    Sorry Bomb #21, I just realized a error in that thinking. While your code works great. It however, on specific tab that info is being copied to, if a row is ever deleted, or inserted by hand, that special formating applied would be moved up or down. I.e I am using say 2000th row as a way to hide the formatting. If I go to say the Navy tab and delete a row, it would move row 2000 info up to 1999. Anythoughts on how to maintain, of hard code the exact formatting into the VBA itself, thus eliminating that possibility??
     
  14. Galstar

    Galstar Thread Starter

    Joined:
    Feb 14, 2007
    Messages:
    23
    So wouldn't it be better to say hide a sheet with all the formatting I may need, then just copy the format from that sheet to anywhere I need. We are copying info around, but how do I copy format from a specific sheet without moving the active sheet?

    So I want to copy the format from row1 in sheet called template to sheet in this formaula
    Sheets(Cells(ActiveCell.Row, 2).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0). which if you rememebr is based on a changing value.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Galstar, do you have Access?
    Because the things that you are learning to do with Bomb's great help :) (y) , is what Access is used for.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544053

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice