Copy Existing Sheet and Paste Special Values with a New Name

Guzzlr · Jan 7, 2019

Hello All,
I’m having a few issues with the Code:

Code:

    Dim sht As Worksheet
    Dim ASY As Worksheet
    Dim DATA As Worksheet
    Set DATA = Sheets("DATA")
    DATA.Copy
       Selection.PasteSpecial Paste:=xlPasteValues
'    After:=DATA
            Columns("N:N").Select
    Selection.AutoFilter
    ActiveSheet.Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
    Sheets("DATA (2)").Name = "ASY"
 
    Set ASY = Sheets("ASY")
    ASY.Move After:=DATA
    ASY.Range("A1").Select

I wish to copy the sheet of DATA, then paste as Special Values to a new tab named ASY. Then filter Column N:N with the criteria of RAMP
Thanks for the help

mumps · Jan 7, 2019

Try:

Code:

Sub CopySheet()
    Application.ScreenUpdating = False
    Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
    Sheets("DATA").UsedRange.Copy
    Cells(1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
    Application.ScreenUpdating = True
End Sub

JoeMo · Jan 7, 2019

Code:

Sub CopyDATA()
    Dim DATA As Worksheet
    Set DATA = Sheets("DATA")
    DATA.Copy after:=DATA
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .Name = "ASY"
        .Columns("N:N").AutoFilter Field:=1, Criteria1:="RAMP"
        .Range("A1").Select
    End With
    
End Sub

Guzzlr · Jan 7, 2019

Thanks for the help
Both of these are working. However, and this is my fault, after the filter. I need to copy and paste once again to special values of the filtered "RAMP", so no rows are jumped/missed.
Sorry for the confusion.
Thank you

mumps · Jan 7, 2019

I'm not sure what you mean. Can you explain in more detail?

Guzzlr · Jan 7, 2019

mumps said:
I'm not sure what you mean. Can you explain in more detail?

So once the column is filtered for RAMP, the sheet only displays the RAMP rows associated. Which is what we want. However, because of the filter, the rows jump from say: row1 to row 13 to row 56...
If I were to copy the filtered sheet, and then paste as values, the rows would no longer skip, and be sequentially numbered.
I'm not sure how to explain it.
Maybe filter first, and then paste as special values to the new sheet.
Thanks for the help

mumps · Jan 7, 2019

Try:

Code:

Sub CopySheet()
    Application.ScreenUpdating = False
    Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
    With Sheets("DATA")
        .Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
        .Range("$A$1:$N$3122").SpecialCells(xlCellTypeVisible).Copy
        Cells(1, 1).PasteSpecial xlPasteValues
        .Range("N1").AutoFilter
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Guzzlr · Jan 7, 2019

mumps said:

Try:

Code:

Sub CopySheet()
    Application.ScreenUpdating = False
    Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
    With Sheets("DATA")
        .Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
        .Range("$A$1:$N$3122").SpecialCells(xlCellTypeVisible).Copy
        Cells(1, 1).PasteSpecial xlPasteValues
        .Range("N1").AutoFilter
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

This gave me only the A1:N1 header only, no data. Thanks

Guzzlr · Jan 7, 2019

Code:

 .Range("$A$1:$AO$3122").SpecialCells(xlCellTypeVisible).Copy

I changed this line to AO instead of N, which gives me the entire Header Row. I do have code working for Last Row:

Code:

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Can't we simply use LastRow instead of $AO$3122
Thanks for the help

mumps · Jan 7, 2019

Code:

.Range("A1:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy

Copy Existing Sheet and Paste Special Values with a New Name

Guzzlr

Well-known Member

Excel Facts

mumps

Well-known Member

JoeMo

MrExcel MVP

Guzzlr

Well-known Member

mumps

Well-known Member

Guzzlr

Well-known Member

mumps

Well-known Member

Guzzlr

Well-known Member

Guzzlr

Well-known Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Copy Existing Sheet and Paste Special Values with a New Name

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock