Consulting

Results 1 to 19 of 19

Thread: Column Autochange?

  1. #1
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location

    Column Autochange?

    Hi,

    I've written a short marco to filter worksheet "Paste_Sheet", and update Columns in worksheet "13 Program UX Support (2)" from Columns in Worksheet "Paste_Sheet"...How I've done this is below....

    [VBA]
    Private Sub Update_Click()
    '============================================
    ' PROCESS FEATURE CANDIDATE
    '============================================
    Dim LastRow As Long
    Dim dst As Worksheet
    Set dst = Sheets("13 Program UX Support (2)")


    With Sheets("Paste_Sheet")
    'SETTING LAST ROW TO THE LAST OCCUPIED CELL
    LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
    'ENSURING AUTOFILTERING IS DISABLED
    .AutoFilterMode = False

    With .Range("A1:N1")
    .AutoFilter
    'FILTERING FOR VALUE IN CELL "I371"
    .AutoFilter Field:=9, Criteria1:=Sheets("Paste_Sheet").Range("I371").Value
    '.AutoFilter Field:=14, Criteria2:=Sheets("Paste_Sheet").Range("N803").Value
    End With

    'COPYING AND PASTING CONTENT OF RANGES
    .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("A6")
    .Range("I2:I" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("B6")
    .Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("C6")
    .Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("D6")
    .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("E6")
    .Range("H2:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("F6")
    .Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("G6")

    'DELETING CONTENT OF THE COLUMN
    .Columns("A:O").Delete

    End With
    End Sub

    [/VBA]

    As you can see the data is getting pasted into columns A --> G, this is what is required, however, there are also columns H --> M that contains data that relates to the data stored in columns A --> G BEFORE the update. So for example Col A, Row 6 is Id no 1645, when you scroll across to columns H -- > M...H6, I6, J6 etc contains review data in relation to ID 1645 stored in A6.

    So after all that waffle my question is this, is it possible, that when pasting into columns A --> G that data in Columns H --> M move to relative rows...

    So after updating, Row 6 (A --> G) might move to row 9 (A --> G), is it possible to for Row 6 (H --> M) move to Row 9 (H --> M)?

    I'm just using row 6 as an example, this would be a requirement for each row.

    If anyone out there could help I would be extremely grateful as I'm relatively new to VBA

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thats quite confusing, can you suply a sample workbook with before and after sheets?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    I am having difficulty attaching the workbook so I've uploaded it to dropbox, click the link below and the workbook will download.

    http://db.tt/tqL08tkv

    Worksheet "13 Program UX Support (B)" is before the update and Worksheet 13 Program UX Support (A) is after the update. As you can see, in "13 Program UX Support (B)" H6, I6, J6...etc is data related to ID stored in A6, which is 1645. on "13 Program UX Support (A)" this ID has moved but the data stored in H6, I6, J6...etc which relates to ID 1645 has stayed in the same cells.

    I hope this clarifies it for you, apologies for the confusion

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You do a simple copy into columns A:H. What should be in I:M?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    Hi xld, I copy in to Columns A:G, the data in these columns are reviewed and reviewers comments are in Columns H:M...

    I hope this makes things clearer, apologies..

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Not really. I wanted to know where to get the data for I:M.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    The data in columns H:M are inputted by the reviewer. It's not pasted in from any other source, when the sheet is updated, columns A:G are changed, I need columns H:M to change with it...

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    But column H from the source is already going to F, M is going to G, and I is going to B, so your explanation doesn't hold together to me.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    The sheet I'm pasting to "13 Program UX Support (2)"... There is already data in columns A:M, when I click the update button, I'm pasting to Columns A:G, which will update Columns A:G, existing records will be edited and records are added, my issue is on "13 Program UX Support (2)" worksheet Columns H:M. The records already in columns A:G have been reviewed and the reviewers comments are stored in columns H:M.

    The issue is when A:G are updated, H:M aren't, so the comments stored in H:M now are now in the wrong rows and are associated with the wrong records..

    Does my explanation hold together now?

  10. #10
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    You don't currently have a 13 Program UX Support (2).
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    apologies "13 Program UX Support (A)"

  12. #12
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Please attach a sample (with two or three rows of data) workbook with a before and after result. We are somewhat confused with which data is going where and when.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    All you have to do is figure out which columns in PasteSheet get copied to columns H:M on 13 Program UX ... nd then copy this line

    [vba].Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("G6") [/vba]

    changing the ranges as appropriate.

    I give up trying to squeeze the information out of you.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    Apologies xld if you couldn't understand my explanation. I already have those lines in my code for copying.

    Below is a drop box link with a sample of my workbook, I had problems attaching it to the post I've renamed the tabs before and after. So before is how it will look before I update, after the update you can see existing data has moved and new data has been introduced. My problem is, on the before tab in rows H:M the reviewers comments are relevant to the data stored in A:G, however, after the update, the comments are no longer relevant as new data is introduced, existing data has moved, but H:M remains static. These comments are inserted manually by reviewers, so once a week the spreadsheet will be updated so I need the reviewers comments to stay on the same row as its relevant data. I hope that has explained it better. I put in comments on the workbook.

    http://db.tt/8kdZcuYH

  15. #15
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Righto.... I've had a look at the new file. Of the before data and the after data only information relating to 1645 is consistent. You would therefore like the data in columns H:M that relates to 1645 to be retained after the update and applied to the row that 1645 now resides in.

    Is this correct?

    BTW: Not sure what you mean by "problems attaching a file". Did you click on Go Advanced, scroll down to Manage Attachments, and follow the prompts?

    Can I make yet another suggestion? Maybe this is more of an Access database than a simple Excel database?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  16. #16
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    @ Aussiebear That is exactly it!!

    However, it won't only be ID 1645, as that is a sample it only contains a few records. So each week this will be updated, once updated, reviewers comments will be added to H:M relevant to certain ID's stored in Column A. It will then be updated the following week so I would like the data in H:M that relates to ID's to be applied after the update and applied to the row that the ID resides...

    I'm so happy I've finally explained it correctly

    As regards to your suggestion about access...I'm a student on placement and I'm acting on the managers instructions, these are his requirements so I'm trying to fulfill these for him.

    Do you think this is possible??

    Thanking you in advance Aussiebear!!
    Last edited by DJ-DOO; 04-13-2012 at 03:02 AM.

  17. #17
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    Hi Guys..I managed to sort that, so thank you very much for all your assistance...


  18. #18
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    And your solution was?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  19. #19
    VBAX Regular
    Joined
    Mar 2012
    Posts
    15
    Location
    I checked the contents of each cell in Col A in the source sheet against each cell in Col in the dest sheet. If it exists, I paste the contents of the appropriate cells across columns A:G, if not, then I added the row to the next available row in Dest sheet.

Posting Permissions

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