I can't get the code to copy the formula, only the values.

[s7ab_m3]

Hello everyone. I have a sheet that populates a portion of another sheet based on criteria in one of the columns. The code I am using is based (almost exactly) off of a bit another user posted on here and it has worked fantastically. That is, except for when it comes to copying the formulas present in the rows being copied. It copies the values, but not the formulas. The reference locations are the same on both sheets, but the values in some of the reference cells change so it leads to incorrect numbers. Below is the code. I tried modifying it myself in various ways but have failed pretty universally. Any help is greatly appreciated!

Option Explicit

Private Sub Worksheet_Activate()

Dim LR As Long

Worksheets("SheetC").Range("A26:z100").Clear 'clear existing data

With Sheets("SheetI")
.AutoFilterMode = False 'remove any prior filtering
.Rows(25).AutoFilter 'activate autofilter
.Rows(25).AutoFilter 3, "=1" 'filter column C for 1
LR = .Range("B" & .Rows.Count).End(xlUp).Row 'is any data visible?
If LR > 1 Then
.Range("A26:H" & LR).Copy Range("A26") 'copy any data visible to report
Else
Range("A26") = "no data found" 'if none, give that message
End If
.AutoFilterMode = False 'turn off autofilter
End With

End Sub



Edit: Formulars are found in Column H rows 26 - 44 (rows differ based on autofilter)

[Obsessed]

I'd try separating out the worksheets first. Neither of your sheets are dimmed so I'd be wondering if the Range.Copy command isn't functioning as you'd expect due to that...it will copy to whatever Range is in the activesheet. Just bad practice. Also, make sure your wrap your code in code tags. I'd recommend uploading a copy of the workbook you're working in so users can bet a better understanding of what you're doing.

[s7ab_m3]

Thanks for the reply Obsessed!

Bad form is what I do! I'm pretty amateur and usually just modify existing code to make it work for what I need. Writing my own turns into a debug nightmare. Someday I'll learn. Anyway, now attached is a sanitized version of my workbook. Copying into the active sheet is fine for this purpose as the two sheets are almost identical. If you have a better method I am, of course, open to it.

Thanks again for your help!

Test.xlsm

[stnkynts]

Since it is part of a worksheet_activate event you technically don't need to declare the sheet (since the respective sheet will be the active sheet). However, as mentioned it is still good practice. Change this line:

Please Login or Register  to view this content.

to these two lines:

Please Login or Register  to view this content.

Does that accomplish what you want?

[s7ab_m3]

That works! Thank you so much for helping a noob!