Copying values and not formulas

[jalrs]

Good morning guys.

When I started preparing for my presentation, I moved the code over to the final version of my sheet, I was using a bogus information one, due to sensitive data in order to be able to share here. I noticed that some values got copied wrong to the templates and I believe this is because I am copying the formulas and not the values. My code follows next and also two attachments to provide an example. It's a tiny detail that didn't occur me before when "playing" with bogus information sheet, but when working with the real sheet it can make a difference when sending the templates to the departments.

Source attachment are the values that are supposed to be copied to destination attachment.

I also highlighted as a comment where I think the problem resides.

Option Explicit
Sub filtromacro1()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, i As Long
Dim mypath As String, docname As String, valorfiltro As String

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Stock")
Set ws2 = wb1.Worksheets("MACRO 1")

lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row

ws2.Activate

    For i = 2 To lr2
      
        valorfiltro = Cells(i, 1).Value
      
        Workbooks.Open Filename:=ThisWorkbook.Path & "\Temp\ST_TEMPLATE_" & Cells(i, 1).Value & ".xlsx"
      
        Set wb2 = Workbooks("ST_TEMPLATE_" & valorfiltro & ".xlsx")
      
        Set ws3 = wb2.Worksheets("Pendentes")
      
        ws3.Activate
      
        ws3.UsedRange.Offset(1).ClearContents
      
        lr3 = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
      
        ws1.Activate
      
        With ws1.Range("A5:AV" & lr1)
      
            .AutoFilter 46, valorfiltro
            .AutoFilter 47, "Em tratamento"
          
            With ws1
      
            .Range("A6:AV" & lr1).Copy ws3.Cells(2, 1) ' i think the problem is here most likely, just don't know how to sort it out
            .Range("BH6:BH" & lr1).Copy ws3.Cells(2, 49) ' same as above line
          
            End With
          
            .AutoFilter
      
        End With
      
        lr3 = ws3.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
        ws3.Range("A" & lr3 & ":A1001").EntireRow.Delete
      
        wb2.Activate
      
        ws3.Activate
      
        lr4 = Cells(Rows.Count, "AT").End(xlUp).Row
      
        If lr4 > 1 Then
      
            Range("AY2:AY" & lr4).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
          
        End If
      
        ws3.Protect Password:="blabla", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=False, _
        AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, _
        AllowInsertingColumns:=False, _
        AllowInsertingRows:=False, _
        AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, _
        AllowDeletingRows:=False, _
        AllowSorting:=True, _
        AllowFiltering:=False, _
        AllowUsingPivotTables:=False

        mypath = ThisWorkbook.Path & "\Anexos\"
          
        wb1.Activate
      
        ws2.Activate
      
        docname = Cells(i, 5).Value
      
        wb2.Activate
      
        ws3.Activate
      
        ActiveWorkbook.SaveAs Filename:=mypath & docname & ".xlsx", FileFormat:=xlOpenXMLWorkbook
  
        ActiveWorkbook.Close
      
    Next i
  
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Any help is greatly appreciated, this is the second last step to finish my project!


Thanks

EDIT: I already tried adding .PasteSpecial Paste:=xlPasteValues but returns an "expected end statement" error

 

[rollis13]

Did you use this syntax for your .PasteSpecial ?

With ws1
    .Range("A6:AV" & lr1).Copy
    ws3.Cells(2, 1).PasteSpecial Paste:=xlPasteValues
    .Range("BH6:BH" & lr1).Copy
    ws3.Cells(2, 49).PasteSpecial Paste:=xlPasteValues
End With

 

[jalrs]

Did you use this syntax for your .PasteSpecial ?

With ws1
    .Range("A6:AV" & lr1).Copy
    ws3.Cells(2, 1).PasteSpecial Paste:=xlPasteValues
    .Range("BH6:BH" & lr1).Copy
    ws3.Cells(2, 49).PasteSpecial Paste:=xlPasteValues
End With

Hey Rollis, Thanks for your reply.

No, I actually didn't try that syntax at all, even tho I tried a lot of stuff.
I eventually came up with:

          With ws1
        
            .Range("A6:AV" & lr1).Copy
            
                With ws3
                
                .Cells(2, 1).PasteSpecial (xlPasteValues)
                
                End With
            
            .Range("BH6:BH" & lr1).Copy
            
                With ws3
                
                .Cells(2, 49).PasteSpecial (xlPasteValues)
                
                End With
                
            End With

But it's hard coded I believe. I will keep with your solution and will mark it as so.

One question, when running both syntaxes the last destination column goes grey, how to prevent this? See attachment please.

Thanks

 

[rollis13]

That's probably because it's selected. Just select another cell just after ending your cycle but I think, since your macro continues, what leaves your cells selected is elsewhere (just move my attached suggestion at the end of your macro); you can also clear Clipboard.

'...
    ws3.Cells(2, 49).PasteSpecial Paste:=xlPasteValues
End With
ws3.Range("A1").Select  '<- or any other range
Application.CutCopyMode = False
'...

 

[jalrs]

That's probably because it's selected. Just select another cell just after ending your cycle but I think, since your macro continues, what leaves your cells selected is elsewhere (just move my attached suggestion at the end of your macro); you can also clear Clipboard.

'...
    ws3.Cells(2, 49).PasteSpecial Paste:=xlPasteValues
End With
ws3.Range("A1").Select  '<- or any other range
Application.CutCopyMode = False
'...

Thanks Rollis, I will try that method tomorrow, I had thought already about that cutcopymode but since I'm not quite familiar with what it does exactly, I decided not to risk it.

I believe it will be sorted, nonetheless I'll tell you, so you know too.

Thanks again

 

[jalrs]

That's probably because it's selected. Just select another cell just after ending your cycle but I think, since your macro continues, what leaves your cells selected is elsewhere (just move my attached suggestion at the end of your macro); you can also clear Clipboard.

'...
    ws3.Cells(2, 49).PasteSpecial Paste:=xlPasteValues
End With
ws3.Range("A1").Select  '<- or any other range
Application.CutCopyMode = False
'...

Hello Rollis,

The Application.CutCopyMode = False line works fine, the above one displays me an error :

" Run time error 1004: Select Method of Range class failed "

Thanks

 

[rollis13]

Maybe sheet 'ws3' isn't the active sheet at the moment.
Anyway, what you need to do is select a cell of the sheet where you see the last destination column gone grey (post #3).

 

[jalrs]

Maybe sheet 'ws3' isn't the active sheet at the moment.
Anyway, what you need to do is select a cell of the sheet where you see the last destination column gone grey (post #3).

Sure, Thanks for the input, I'll sort it out!

Thank you Rollis

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.