Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    When finished move to difference sheet (XP)

    I have been asked to help a local art gallery with a spreadsheet to control the artworks. I have made all the necessary columns. One of these columns is "hanging fees" and when the hanging fee is paid by the artist, the number and the name of the artist change colour (using conditional formatting) so you can see directly who has paid. No problem there.

    When the gallery sells the artwork, they pay the artist the price of the painting minus their commission. No problems with the fomulae either.

    What I would like to know, and I have no idea where to start, is that when the whole transaction is finised, the row of information goes to another sheet in the same workbook, called history of something like that. I suppose I have to have a macro that says something like, "when finished go the the next empty row"

    So in the end only the works in the gallery are listed and the sold ones are separate.

    Your help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: When finished move to difference sheet (XP)

    It would be possible to use a worksheet event to do this automatically, but I think it would be very disconcerting for the users if a row vanished the moment they mark an artwork as sold. Instead, make a macro available through a command button, a custom toolbar button and/or a keyboard shortcut. The macro could look like this:

    Sub MoveSold()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim i As Long
    Dim n As Long
    Dim j As Long
    Const strFilledCol = "A"
    Const strFinishCol = "E"

    Set wshSource = Worksheets("For Sale")
    Set wshTarget = Worksheets("Sold")

    n = wshSource.Range(strFilledCol & 65536).End(xlUp).Row
    j = wshTarget.Range(strFilledCol & 65536).End(xlUp).Row
    For i = n To 2 Step -1
    If wshSource.Range(strFinishCol & i) <> "" Then
    j = j + 1
    With wshSource.Rows(i)
    .Copy Destination:=wshTarget.Rows(j)
    .Delete
    End With
    End If
    Next i

    Set wshTarget = Nothing
    Set wshSource = Nothing
    End Sub

    Here, column A is a column that is guaranteed to be filled for all art works, whether sold or not, and column E is the column that is blank until an art work is sold, then filled in.
    The worksheets are named 'For Sale' and 'Sold'. Modify these values in the code as needed.
    I assumed that row 1 in both sheets contains column headings.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When finished move to difference sheet (XP)

    I tried that, but somewhere the macro needs to be debugged. I'm not all that ok with macros.
    Have attached a very simple spreadsheet and you can see where it stops

    Thanks so much

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: When finished move to difference sheet (XP)

    Browsers seem to be playing havoc with "less than" and "greater than" characters today. I have attached a screenshot of what it should be.

    BTW, I don't understand why you have a ChDir at the end of the macro.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When finished move to difference sheet (XP)

    Wow, works like a dream. Thanks so much.
    Don't know about the ChDir end either. That appeared out of nowhere when I changed the security of the workbook.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When finished move to difference sheet (XP)

    The criteria has changed a bit. They now want
    If the word "sold" is in column E that content to go to the sold sheet, but when the word "removed" is in this column that it goes to a sheet called "removed"

    I had a try myself and it didn't work

    Thanks for your help

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: When finished move to difference sheet (XP)

    Try this:

    Sub MoveSold()
    Dim wshSource As Worksheet
    Dim wshSold As Worksheet
    Dim wshRemoved As Worksheet

    Dim i As Long
    Dim n As Long
    Dim jSold As Long
    Dim jRemoved As Long

    Const strFilledCol = "A"
    Const strFinishCol = "E"

    Set wshSource = Worksheets("For Sale")
    Set wshSold = Worksheets("Sold")
    Set wshRemoved = Worksheets("Removed")

    n = wshSource.Range(strFilledCol & 65536).End(xlUp).Row
    jSold = wshSold.Range(strFilledCol & 65536).End(xlUp).Row
    jRemoved = wshRemoved.Range(strFilledCol & 65536).End(xlUp).Row
    For i = n To 2 Step -1
    If wshSource.Range(strFinishCol & i) <> "" Then
    With wshSource.Rows(i)
    If wshSource.Range("E" & i) = "Sold" Then
    jSold = jSold + 1
    .Copy Destination:=wshSold.Rows(jSold)
    Else
    jRemoved = jRemoved + 1
    .Copy Destination:=wshRemoved.Rows(jRemoved)
    End If
    .Delete
    End With
    End If
    Next i

    Set wshRemoved = Nothing
    Set wshSold = Nothing
    Set wshSource = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •