Hi Excel Experts,
Been trying to get the solution for this for weeks but to no avail. I have a table with numbers of column that individually consists of validation lists, conditional formatting and formulas. Am trying to automate the consolidated data from different workbook into a master file. After copy+paste, I should do deletion for duplicate records and sort the data after deletion of duplicates. But, my problem is, after removal of duplicates, the validations lists and conditional formats and formulas in the affected cell will also be wiped off. I tried the below macro to auto fill it back. But seems like it only will apply to 1 row only which is the first row which is blank and exactly after the last record row of data. I want to fill them up until a fixed row which is S1004 (fyi, my headers at row 4). can anybody pls help me?
Rws = Application.WorksheetFunction.CountA(Worksheets("All").Range("B:B")) + 4
Range("A1048576:S1048576").Select 'To copy the exact content format/formulas/validation lists
Selection.Copy
Range("A" & Rws).End(xlDown).Offset(1).Select
'Rows (Rws & ":" & Rws) + 100
'Range("A" & Rws).Select
'Range("A" & Rows.Count).End(xlUp).Offset(1).Select
'Range(Selection, Selection.End(xlDown)).Select
'Range("A" & Rws & ":S1004").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B4").Select
Selection.AutoFilter
Many thanks in advance.
~dell~
Been trying to get the solution for this for weeks but to no avail. I have a table with numbers of column that individually consists of validation lists, conditional formatting and formulas. Am trying to automate the consolidated data from different workbook into a master file. After copy+paste, I should do deletion for duplicate records and sort the data after deletion of duplicates. But, my problem is, after removal of duplicates, the validations lists and conditional formats and formulas in the affected cell will also be wiped off. I tried the below macro to auto fill it back. But seems like it only will apply to 1 row only which is the first row which is blank and exactly after the last record row of data. I want to fill them up until a fixed row which is S1004 (fyi, my headers at row 4). can anybody pls help me?
Rws = Application.WorksheetFunction.CountA(Worksheets("All").Range("B:B")) + 4
Range("A1048576:S1048576").Select 'To copy the exact content format/formulas/validation lists
Selection.Copy
Range("A" & Rws).End(xlDown).Offset(1).Select
'Rows (Rws & ":" & Rws) + 100
'Range("A" & Rws).Select
'Range("A" & Rows.Count).End(xlUp).Offset(1).Select
'Range(Selection, Selection.End(xlDown)).Select
'Range("A" & Rws & ":S1004").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B4").Select
Selection.AutoFilter
Many thanks in advance.
~dell~
