VBA to find Correct Column and Copy/Paste Values

[moony]

Hello,

I am working on a sheet called Monthly that automatically pulls the relevant data from the ABR Drop In sheet. The cells confirm the month referenced above in cell H5 matches the date inputted in the ABR Drop In sheet in cell H5. The Monthly sheet has the dates listed in Monthly!$F$5:$EH$5. Only the blue cells are inputs, and the goal is to have a button on the ABR Drop In sheet that will look at the date in cell 'ABR Drop In'!H5, find that column in the Monthly sheet, and copy/paste all the blue input cells as values.

Appreciate any help I can get!

Thanks!

Hotel AM Tracker v1 - test.xlsx
	B	C	D	E	F	G	H
5	Input Month Ending Date of Statements Below:						12/31/2021
ABR Drop In

Hotel AM Tracker v1 - test.xlsx
	H
5	Dec 2021
6	2
7	1
8
9	49
10
11
12
13	1,519
14	458
15	351
16	107
17	30.2%
18	990.66
19	1,240.21
20	396.58
21	298.70
22
23	--
24	--
25
26
27	--
28	--
29
30
31	--
32
33	--
34	--
35
36
37	--
38	--
39
40
41	--
42
43	1,519
44	458
45	351
46	107
47	30.2%
48	990.66
49
50
51	298.70
52
53
54	453,721
55
56
57	215,575
58	116,669
59	8,941
60	25,887
61	971
62	63,107
63	103,983
64	99,743
65	2,153
66	1,800
67	287
68
69	15,602
70	12,600
71
72	46,337
73	1
74	--
75	36,316
76	424
77	9,596
78
79	847,818
80
81
82	242,656
83
84
85	422,221
86	28,207
87
88
89	81,249
90	--
91	--
92	73,960
93	7,289
94	0
95
96	774,333
97
98	73,485
99
100
101	196,141
102	44,272
103	166,620
104	66,466
105	28,330
106
107	501,830
108
109	(428,345)
110
111	21,195
112
113
114	(449,540)
115
116
117	(78,884)
118	12,617
119	5,070
120
121
122
123
124	(61,197)
125
126	(388,343)
127
128	16,927
129
130	(405,270)
Monthly

 

[moony]

Hello,

Any chance my request isn't worded clearly?

 

[moony]

bump

 

[moony]

this is what i created by putting different things together. not sure if this is a good/efficient way, but it seems to work.
I created a dummy workbook to test it out.

Sub PasteMonthlyPL()
 
 Dim rng As Range
 Dim MonthlyPL As String
 Dim colnumber As Long
 
 MonthlyPL = Sheet1.Cells(1, 1)

 Set rng = Sheet2.Rows("1:1").Find(What:=MonthlyPL, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    colnumber = rng.Columns
    Sheet2.Range(Cells(2, colnumber), Cells(4, colnumber)).Copy
    Sheet2.Range(Cells(2, colnumber), Cells(4, colnumber)).PasteSpecial Paste:=xlPasteValues
    Sheet2.Range(Cells(7, colnumber), Cells(9, colnumber)).Copy
    Sheet2.Range(Cells(7, colnumber), Cells(9, colnumber)).PasteSpecial Paste:=xlPasteValues
    Sheet2.Range(Cells(12, colnumber), Cells(14, colnumber)).Copy
    Sheet2.Range(Cells(12, colnumber), Cells(14, colnumber)).PasteSpecial Paste:=xlPasteValues
    Sheet2.Range(Cells(17, colnumber), Cells(18, colnumber)).Copy
    Sheet2.Range(Cells(17, colnumber), Cells(18, colnumber)).PasteSpecial Paste:=xlPasteValues
    Sheet2.Cells(20, colnumber).Copy
    Sheet2.Cells(20, colnumber).PasteSpecial Paste:=xlPasteValues
End Sub

 

[moony]

i copied it over and made the adjustments so it fit the workbook i need, but i'm getting an error at the colnumber = rng.Columns line:

Sub PasteMonthlyPL()
 
 Application.ScreenUpdating = False
 Dim rng As Range
 Dim MonthlyPL As String
 Dim colnumber As Long
 
 MonthlyPL = ActiveWorkbook.Sheets("ABR Drop In").Cells(5, 8)
 Sheets("Monthly").Activate

 Set rng = ActiveSheet.Rows("5:5").Find(What:=MonthlyPL, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    colnumber = rng.Columns
    
    ActiveSheet.Cells(9, colnumber).Copy
    ActiveSheet.Cells(9, colnumber).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Cells(54, colnumber).Copy
    ActiveSheet.Cells(54, colnumber).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(58, colnumber), Cells(62, colnumber)).Copy
    ActiveSheet.Range(Cells(58, colnumber), Cells(62, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(64, colnumber), Cells(67, colnumber)).Copy
    ActiveSheet.Range(Cells(64, colnumber), Cells(67, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(69, colnumber), Cells(70, colnumber)).Copy
    ActiveSheet.Range(Cells(69, colnumber), Cells(70, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(73, colnumber), Cells(77, colnumber)).Copy
    ActiveSheet.Range(Cells(73, colnumber), Cells(77, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Cells(82, colnumber).Copy
    ActiveSheet.Cells(82, colnumber).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(90, colnumber), Cells(94, colnumber)).Copy
    ActiveSheet.Range(Cells(90, colnumber), Cells(94, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(101, colnumber), Cells(105, colnumber)).Copy
    ActiveSheet.Range(Cells(101, colnumber), Cells(105, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Cells(111, colnumber).Copy
    ActiveSheet.Cells(111, colnumber).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Range(Cells(117, colnumber), Cells(119, colnumber)).Copy
    ActiveSheet.Range(Cells(117, colnumber), Cells(119, colnumber)).PasteSpecial Paste:=xlPasteValues
    
    ActiveSheet.Cells(128, colnumber).Copy
    ActiveSheet.Cells(128, colnumber).PasteSpecial Paste:=xlPasteValues

Application.ScreenUpdating = True
End Sub

 

[Marc L]

Hello,​

​

of course as this codeline has no sense ! (Just reading VBA help …) So what this codeline is supposed to do ?​

 

[Marc L]

According to your thread title a VBA demonstration to replace all formulas in a found column with their values for starters :​

Sub Demo1()
    Dim V
    With Sheets("Monthly").UsedRange.Columns
        V = Application.Match(['ABR Drop In'!H5].Text, .Rows(5), 0)
        If IsNumeric(V) Then .Item(V).Formula = .Item(V).Value2
    End With
End Sub

 

[moony]

According to your thread title a VBA demonstration to replace all formulas in a found column with their values for starters :​

Sub Demo1()
    Dim V
    With Sheets("Monthly").UsedRange.Columns
        V = Application.Match(['ABR Drop In'!H5].Text, .Rows(5), 0)
        If IsNumeric(V) Then .Item(V).Formula = .Item(V).Value2
    End With
End Sub

thanks for the response!
I'll give it a try and will let you know.

Much appreciated

Edit:
Will this replace all formulas in the column, because there are many rows that should not be replaced with values. In the example above, only the rows with the blue inputs need to be replaced with values

 

[moony]

is there another solution to this? the above proposed solution converts every formula into a value, which is not the intention. Only the designated blue input rows need to be pasted as values.

Thanks!

 

[Marc L]

So link your workbook on a files host website like Dropbox …​