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

Guzzlr · Jan 8, 2019

mumps said:
Code:

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

The code I'm using is only giving me the column headers from A1:AO1...it's still not wanting to copy the body of data from the filter.
I believe the reason is the DATA sheet is an excel "Refresh All" Query into the company network. I believe to get it to work, the DATA sheet must first be copied and paste as Values into it's own sheet, and then run the code below.
Thanks for the help

Code:

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox "Last Non-Blank Row in Column-A Is " & LastRow

Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
    With Sheets("DATA")
        .Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
        .Range("A1:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        Cells(1, 1).PasteSpecial xlPasteValues
        .Range("N1").AutoFilter
    End With
    Application.CutCopyMode = False

Guzzlr · Jan 8, 2019

The code I'm using is only giving me the column headers from A1:AO1...it's still not wanting to copy the body of data from the filter.
I believe the reason is the DATA sheet is an excel "Refresh All" Query into the company network. I believe to get it to work, the DATA sheet must first be copied and paste as Values into it's own sheet, and then run the code below.
I'm seeing on the DATA sheet, where the filter is in place, however, it is not filtering the word "RAMP"...and "RAMP" is in column N. It skips over finding the word, and moves directly to copy the row 1
Thanks for the help
Thanks for the help

mumps · Jan 8, 2019

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Guzzlr · Jan 8, 2019

Whats your private email

Guzzlr · Jan 8, 2019

mumps said:
I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using.

Can we try it with out making the file public, there is company info on this file.

Guzzlr · Jan 8, 2019

Guzzlr said:
Can we try it with out making the file public, there is company info on this file.

What if code were written to copy and paste special values to a temporary named tab, then run your code off the temp. tab.
your code will work off a copied special values from the DATA tab.

mumps · Jan 8, 2019

Try filtering the sheet manually on the word "RAMP". Does it work?

Guzzlr · Jan 8, 2019

mumps said:
Try filtering the sheet manually on the word "RAMP". Does it work?

Code:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox "Last Non-Blank Row in Column-A Is " & LastRow
    Sheets("DATA").Cells.Copy
Sheets("TEMP").Range("A1").PasteSpecial xlPasteValues
    Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
    With Sheets("TEMP")
        .Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
        .Range("$A$1:$AO$3122").SpecialCells(xlCellTypeVisible).Copy
        Cells(1, 1).PasteSpecial xlPasteValues
        .Range("N1").AutoFilter
    End With
    Application.CutCopyMode = False

This is working, copying the DATA sheet into the TEMP sheet, and then running your original code. It's messy, but it works, is there a better way to code it? Also, I suppose the TEMP sheet should be deleted after it's used.
Thanks for the help

mumps · Jan 8, 2019

Try:

Code:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox "Last Non-Blank Row in Column-A Is " & LastRow
Sheets("DATA").Cells.Copy
Sheets("TEMP").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Worksheets.Add(After:=Sheets("DATA")).Name = "ASY"
With Sheets("TEMP")
    .Range("$N$1:$N$3122").AutoFilter Field:=1, Criteria1:="RAMP"
    .Range("$A$1:$AO$3122").SpecialCells(xlCellTypeVisible).Copy
    Cells(1, 1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
End With

Guzzlr · Jan 9, 2019

Thank you mumps for your help, this is working. I may have to revisit this thread because I may need to filter more than one Criteria.
thanks

Copy Existing Sheet and Paste Special Values with a New Name

Guzzlr

Well-known Member

Guzzlr

Well-known Member

Excel Facts

Guzzlr

Well-known Member

mumps

Well-known Member

Guzzlr

Well-known Member

Guzzlr

Well-known Member

Guzzlr

Well-known Member

mumps

Well-known Member

Guzzlr

Well-known Member

mumps

Well-known Member

Guzzlr

Well-known Member

Similar threads

Forum statistics

Share this page

Copy Existing Sheet and Paste Special Values with a New Name

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

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