DJ-DOO
04-12-2012, 06:00 AM
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
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