Column Autochange?

[DJ-DOO]

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....

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

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

[Simon Lloyd]

Thats quite confusing, can you suply a sample workbook with before and after sheets?

[DJ-DOO]

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

[Bob Phillips]

You do a simple copy into columns A:H. What should be in I:M?

[DJ-DOO]

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..

[Bob Phillips]

Not really. I wanted to know where to get the data for I:M.

[DJ-DOO]

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...

[Bob Phillips]

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.

[DJ-DOO]

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?

[Aussiebear]

You don't currently have a 13 Program UX Support (2).

[DJ-DOO]

apologies "13 Program UX Support (A)"

[Aussiebear]

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.

[Bob Phillips]

All you have to do is figure out which columns in PasteSheet get copied to columns H:M on 13 Program UX ... and then copy this line

.Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("G6")

changing the ranges as appropriate.

I give up trying to squeeze the information out of you.

[DJ-DOO]

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

[Aussiebear]

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?

[DJ-DOO]

@ 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!!

[DJ-DOO]

Hi Guys..I managed to sort that, so thank you very much for all your assistance...

[Aussiebear]

And your solution was?

[DJ-DOO]

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.