Macro copy master sheet to end of workbook, identical sheet with formula values

  • I have this almost working like I need, but there is something missing. I am copying the active "master" sheet, to the end of the workbook, and renaming it based on certain cell values. The code is copying the sheet just like I want, but it's copying the cell formulas also, and I need the cell values. I've tried the xlPasteSpecial Paste:=PasteValues and I've tried Paste:=PasteFormats and Paste_=PasteAll but none of these do what I'm trying to accomplish. Much needed help. My code is as follows:

    Code
    Dim strName As String, strSht As String
        strName = "DMR" & " " & Range("S1").Value
        strSht = ActiveSheet.Name
        
        Application.ScreenUpdating = False
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = strName
        Sheets(strSht).Range("A1:Y89").Copy
        Sheets(strName).Range("A1").PasteSpecial Paste:=xlPasteAll


    Can anyone help me with this dilemma please and tia...

  • Re: Macro copy master sheet to end of workbook, identical sheet with formula values


    Should work if you just change the last line to:

    Code
    Sheets(strName).Range("A1:Y89").PasteSpecial(xlPasteValues)
  • Re: Macro copy master sheet to end of workbook, identical sheet with formula values


    Thanks for the help Trevor_S but that didn't work at all. I ended up pasting the values, and then another line pasting the formats and it worked great:

    Code
    Sheets(strName).Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheets(strName).Range("A1").PasteSpecial Paste:=xlPasteFormats
  • Re: Macro copy master sheet to end of workbook, identical sheet with formula values


    Not sure if I mentioned it or not but I'm coding in Excel VBA 2013. I've noticed that with 2010 and 2013, the code is a little different with some syntax and all..thanks again

  • Re: Macro copy master sheet to end of workbook, identical sheet with formula values


    That may be why ... I'm still using 2007! Also I hadn't realised that you still wanted the formats, you would have needed an extra line for that.
    Glad its working now though!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!