none
paste special values RRS feed

  • Question

  • See question marks below:

    Sub BuildInvoices()
        Dim wsCurrent As Worksheet
        Dim wsTemp As Worksheet
        Dim numbNames As Integer

        'This finds a list of names to make invoices for:
        Sheets("Data").Select
        Set wsCurrent = ActiveSheet
        'Application.ScreenUpdating = False
        Range("f3", Range("f3").End(xlDown)).Copy Range("W1")
        Range("W1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
        numbNames = Range("W1").CurrentRegion.Rows.Count - 1
       
        'This removes the autofilter
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
       
        'This loops through the list of names, making a worksheet for each one
        Do While numbNames > 0
          
            Sheets("Invoice Template").Select
            Set wsTemp = ActiveSheet
            wsTemp.Copy After:=Sheets("Invoice Template")
            ActiveSheet.Name = "Temp Invoice"
            With wsCurrent.Range("A2").CurrentRegion
                .AutoFilter Field:=6, _
                    Criteria1:=wsCurrent.Range("W1").Offset(numbNames).Value
                .AutoFilter Field:=9, Criteria2:="="
               
                .Copy Destination:=Sheets("Temp Invoice").Range("A16")     'How do I make it paste as values here so my formatting doesn't change??????????????????????????????????????????????????????????????????????????????????????????
               
                .AutoFilter
            End With
            ActiveSheet.Name = wsCurrent.Range("W1").Offset(numbNames).Value
            numbNames = numbNames - 1
        Loop
        wsCurrent.Range("W1").CurrentRegion.Clear
        'Application.ScreenUpdating = True
       
        Rows(2).AutoFilter
    End Sub

    Tuesday, August 13, 2013 3:09 AM

Answers

  • Change

                .Copy Destination:=Sheets("Temp Invoice").Range("A16")

    to

                .Copy
                Sheets("Temp Invoice").Range("A16").PasteSpecial Paste:=xlPasteValues


    Regards, Hans Vogelaar

    • Marked as answer by pickytweety Sunday, November 17, 2013 7:04 PM
    Tuesday, August 13, 2013 5:27 AM