VBA to Paste values if condition met

[dogpile]

Hi All!
I'm looking for VBA code to copy and paste values in the range D37:F54 in the below example, but for only the rows that are not sub-totals (i.e. paste values for accounts that do not start with a "PL")
Right now all rows have formulas in them that populate the data from somewhere else in the sheet.
Thanks in advance!

Excel 2013 64 bit
	C	D	E	F
32		2018	2018	2018
33		Actual	Forecast	Forecast
34		January	February	March
35	ACCOUNT
36
37	420010 - Base Rent Revenue	346,620	346,451	347,202
38	PL410_RES - Gross rent	346,620	346,451	347,202
39	426050 - Base Rent Vacancy - Offset	-	(24,102)	(20,487)
40	PL420_RES - Building Vacancy	-	(24,102)	(20,487)
41	426020 - New Lease Promo	-	(1,215)	(1,215)
42	PL425_RES - Promotions	-	(1,215)	(1,215)
43	424010 - Parking Rental Revenue	21,292	20,667	20,667
44	PL430_RES - Parking revenue	21,292	20,667	20,667
45	424040 - Other Rental Income	-	-	-
46	424040_RENTAL - Other Rental Income for rental	-	1,350	1,350
47	428070 - Utilities Recovery	(8,253)	6,500	6,500
48	480130 - Service Revenue	(147)	83	83
49	480150 - Miscellaneous Revenue	-	-	-
50	480150_RENTAL - Miscellaneous Revenue - Revenue for rental	120	100	100
51	480190 - Telecommunication Commission Revenue	(6,825)	1,491	1,491
52	PL499_RES - Other income	(15,105)	9,524	9,524
53	PL490_RES - All other income	(15,105)	9,524	9,524
54	PL400_RES - Total Revenue	352,807	351,326	355,691
Sheet1

 

[mumps]

Try:

Sub PasteValues()
    Application.ScreenUpdating = False
    Range("C36:F54").Select
    Selection.AutoFilter
    Range("C36:F54").AutoFilter Field:=1, Criteria1:="<>*PL*"
    Range("D37:F54").SpecialCells(xlCellTypeVisible).Value = Range("D37:F54").SpecialCells(xlCellTypeVisible).Value
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[dogpile]

Thanks for your reply - this almost got me there - only issue, it looks like it's pasting the values of the first row to all rows (other than the rows with "PL" which is good).
Here is the result:

Excel 2013 64 bit
	C	D	E	F
32		2018	2018	2018
33		Actual	Forecast	Forecast
34		January	February	March
35	ACCOUNT
36
37	420010 - Base Rent Revenue	346,620	346,451	347,202
38	PL410_RES - Gross rent	346,620	346,451	347,202
39	426050 - Base Rent Vacancy - Offset	346,620	346,620	346,620
40	PL420_RES - Building Vacancy	-	(24,102)	(20,487)
41	426020 - New Lease Promo	346,620	346,451	347,202
42	PL425_RES - Promotions	-	(1,215)	(1,215)
43	424010 - Parking Rental Revenue	346,620	346,620	346,620
44	PL430_RES - Parking revenue	21,292	20,667	20,667
45	424040 - Other Rental Income	346,620	346,451	347,202
46	424040_RENTAL - Other Rental Income for rental	346,620	346,451	347,202
47	428070 - Utilities Recovery	346,620	346,451	347,202
48	480130 - Service Revenue	346,620	346,451	347,202
49	480150 - Miscellaneous Revenue	346,620	346,451	347,202
50	480150_RENTAL - Miscellaneous Revenue - Revenue for rental	346,620	346,451	347,202
51	480190 - Telecommunication Commission Revenue	346,620	346,451	347,202
52	PL499_RES - Other income	(15,105)	9,524	9,524
53	PL490_RES - All other income	(15,105)	9,524	9,524
54	PL400_RES - Total Revenue	352,807	351,326	355,691
Sheet1

 

[mumps]

Try:

Sub PasteValues()
    Application.ScreenUpdating = False
    Dim rng As Range
    Range("C36:F54").AutoFilter Field:=1, Criteria1:="<>*PL*"
    For Each rng In Range("D37:D54").SpecialCells(xlCellTypeVisible)
        Range("D" & rng.Row & ":F" & rng.Row).Copy
        Range("D" & rng.Row).PasteSpecial xlPasteValues
    Next rng
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[dogpile]

Thanks for this - it seems to work now, but as soon as I amend the code to accommodate a larger table (i.e. c36:AA500") the run time is extremely slow.
Back to the drawing board...

 

[Fluff]

How about

Sub PasteValues()

    Dim Rng As Range
    
    Range("C36:F36").AutoFilter 1, "<>*PL*"
    For Each Rng In Range("D37", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Areas
      Rng.Resize(, 24).Value = Rng.Resize(, 24).Value
    Next Rng
    ActiveSheet.AutoFilterMode = False
End Sub

 

[dogpile]

Thanks so much! That sped things up dramatically! Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback