Need a VBA Doctor! (Code works for original intention, need to tweak)

[azj5001]

Warning: I'm not a VBA expert, I'm a Google and steal research others code to solve my unique problems. I have a basic understanding of VBA and usually can figure out problems with some Google foo, but this issue has stumped me.

I have the following code, which works perfectly for my original intention. The purpose of the code is to generate a sheet for every unique value in column 3, and move the data (with formatting) into it's respective named sheet. The original intent was to be a "values only" file, no formulas. We've decided we'd like formulas to be persistent with each tab. The odd thing is I cannot find a "paste special values" step or anything like that. The real head scratcher for me is the first sheet generated HAS the formulas but every subsequent sheet is all values. I'm assuming the "no formulas" step is in the loop portions of the code. Can anyone help me out to modify the following code in order for formulas to be persistent? Maybe it's not possible with how this code is set up, can a expert explain why? I'd love to understand to add this into my knowledge toolset.

Sub Part2()


Dim LR As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 3
Set ws = Sheets("Master")
LR = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A2:EY2"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To LR
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A2)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & LR).EntireRow.Copy Sheets(myarr(i) & "").Range("A2")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim sh As Worksheet
    Dim DateString As String
    Dim FolderName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    
    End Sub

 

[Micron]

Not sure I can answer because I'm more adept at Access vba (learning Excel vba also) but can teach you a way to fish using the Object Browser:
Note that I chose Excel from the object library drop down. Hit binoculars. Notice I chose PasteSpecial of the Range class. Note the syntax at the bottom.
Click on the hyperlinks for more info. Also, help button at the top ? will usually open the web page for the subject matter.

I clicked on XLPasteType and got a list. I gather that you need to choose xlPasteAll - unless you don't want to paste formats.

Using code tags is appreciated, but your code lacks indentation, making it harder to read.

 

[azj5001]

Thanks for the insight. I'll keep poking around!

 

[azj5001]

Not sure I can answer because I'm more adept at Access vba (learning Excel vba also) but can teach you a way to fish using the Object Browser:
Note that I chose Excel from the object library drop down. Hit binoculars. Notice I chose PasteSpecial of the Range class. Note the syntax at the bottom.
Click on the hyperlinks for more info. Also, help button at the top ? will usually open the web page for the subject matter.

View attachment 77209

I clicked on XLPasteType and got a list. I gather that you need to choose xlPasteAll - unless you don't want to paste formats.
View attachment 77210

Using code tags is appreciated, but your code lacks indentation, making it harder to read.

Stepped away from my code for a couple of days, and between your feedback and some research I figured it out. Thank you again for your guidance!

 

[Micron]

So was it xlPasteAll you needed?