Copying cell ranges instead row?

[Mike2502]

Hey All,

I'm trying to select the data from column A till P instead of copying, pasting and deleting the whole rows? please see code below and thanks in advance !

Sub Sort_Data()

    Cells.Select
    ActiveWorkbook.Worksheets("ADO").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ADO").Sort.SortFields.Add(Range("A1:A100"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
        , 0)
    ActiveWorkbook.Worksheets("ADO").Sort.SortFields.Add(Range("B1:B100"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
        , 0)
    With ActiveWorkbook.Worksheets("ADO").Sort
        .SetRange Range("A1:N100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    With Sheets("ADO")
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
    .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row).AutoFilter 2, RGB(255, 0, 0), xlFilterCellColor
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
 
   Range("A2").Select
  
End Sub

 

[MARK858]

Untested but can't you just do something like the below

    With Sheets("ADO")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1:P" & .Range("A" & Rows.Count).End(xlUp).Row)
            .AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
            .Offset(1).Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
            .Offset(1).Delete
            Sheets("ADO").ShowAllData
        End With
        With .Range("A1:P" & .Range("B" & Rows.Count).End(xlUp).Row)
            .AutoFilter 2, RGB(255, 0, 0), xlFilterCellColor
            .Offset(1).Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
            .Offset(1).Delete
            Sheets("ADO").ShowAllData
        End With
    End With

 

[Mike2502]

Untested but can't you just do something like the below

    With Sheets("ADO")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1:P" & .Range("A" & Rows.Count).End(xlUp).Row)
            .AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
            .Offset(1).Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
            .Offset(1).Delete
            Sheets("ADO").ShowAllData
        End With
        With .Range("A1:P" & .Range("B" & Rows.Count).End(xlUp).Row)
            .AutoFilter 2, RGB(255, 0, 0), xlFilterCellColor
            .Offset(1).Copy Sheets("TBD_Manually").Range("A" & Rows.Count).End(xlUp)(2)
            .Offset(1).Delete
            Sheets("ADO").ShowAllData
        End With
    End With

No mate, sorry I didn't explain it clear - So the code I posted selects cells that are the color red and copy pastes the entire row to 'TBD_Manually', however instead of the whole row I want to limit it up to Column A to P?

Hope that makes sense

 

[MARK858]

Doesn't that code do that?

 

[Mike2502]

Doesn't that code do that?

Doesn't that code do that?

Hi Mark - Yes it does haha I just tried it however it still brings across my Macro buttons to 'TBD_Manually' even though they are on columns R,S,T...

Is there any other alternative way to keep the buttons fixed and not be altered?

Thank you btw

 

[MARK858]

Hi Mark - Yes it does

Always better to test first before replying

Is there any other alternative way to keep the buttons fixed and not be altered?

Doesn't bring my form control buttons over on my workbook. You can try changing the buttons properties to don't move or size with cells.

 

[Mike2502]

Always better to test first before replying



Doesn't bring my form control buttons over on my workbook. You can try changing the buttons properties to don't move or size with cells.

Mark mate, you are great, cheers mate..

Bit off topic right, but I want to only allow Columns A and N for other users to input data into but other cells are associated with my macro and codes won't work... is there an alternative mate?

 

[MARK858]

Lock all the cells except those columns, protect the sheet, Put code at the start of the macro to unprotect the sheet and code at the end of the macro to protect the sheet again.

 

[Mike2502]

Lock all the cells except those columns, protect the sheet, Put code at the start of the macro to unprotect the sheet and code at the end of the macro to protect the sheet again.

Thanks mate,

.. I have an absolute vLookup for 50 rows but then lets say 5 rows are deleted and pasted to the next worksheet the last 5 rows don't have no vlookup coding no more.

Any way to keep the vlookups fixed in their cells even when rows are copied, deleted and pasted over without unlocking the sheet and fill all?

 

[MARK858]

Are you saying that you have 50 rows of data with 50 vlookups, you delete 5 rows, you are left with 45 rows with data and the next 5 blank rows have no vlookups in them?

If yes then that is what should happen. If you want it to always have 50 vlookups after deleting then reapply the formula to the range in the VBA.

If it is anything else then please re-explain what is happening.