Find a text value in a column then enter values below that text in same column

[poey]

Hi, long time lurker, first time poster... thanks for your patience with me.

I have a multiple sheet workbook.
In the workbook, each sheet in column A has the word Total in it.
The word Total is on a different row on each sheet.

I am trying to come up with a macro that will find the word Total on each sheet (whether it's on A34 or A96), then pull the value of column B below it.

IE: if the word "Total" is in A34, then A35 should be B35, A36 = B36 and so on.

Thanks in advance for any help you can provide.

 

[VoG]

Try

Sub Test2()
Dim ws As Worksheet, Found As Range
For Each ws In ActiveWorkbook.workshets
    With ws
        Set Found = .Columns("A").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
        If Not Found Is Nothing Then Found.Offset(1).Value = Found.Offset(, 1).Value
    End With
Next ws
End Sub

 

[poey]

Thanks so much for the speedy response!
This worked, but it brought over the cell adjacent to "Totals", not one below.


<TABLE style="WIDTH: 244pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>

Total
​

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 95pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=127>

Agents:
​

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=135>

2
​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>

​

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

Commitmnt/Callbk
​

</TD><TD class=xl3813 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>

0:08
​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>

​

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

Misc-closed
​

</TD><TD class=xl3813 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>

0:46
​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>

​

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">

Total
​

</TD><TD class=xl3813 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>

0:54
​

</TD></TR></TBODY></TABLE>

Trying to get Commitmnt/Callbk , Misc-closed and any other cells that have data to be below the Total.

Thanks again.

 

[VoG]

Then perhaps

Sub Test2()
Dim ws As Worksheet, Found As Range
For Each ws In ActiveWorkbook.workshets
    With ws
        Set Found = .Columns("A").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
        If Not Found Is Nothing Then Found.Offset(1, 1).Value = Found.Offset(, 1).Value
    End With
Next ws
End Sub

 

[poey]

Closer... but this is now moving "Agents" down in column B overtyping what i'm trying to move to column A.

Heading home from work, thanks SO much for your help. Can't express my gratitude enough.

Laura

 

[VoG]

So, do you want to insert a new row?

 

[p45cal]

try:

Sub blah()
For Each sht In ThisWorkbook.Sheets
  Set TotalRng = sht.Range("A:A").Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
  If Not TotalRng Is Nothing Then
    'Stop 'F8 to step through code, F5 to continue.
    offst = 1
    Do Until TotalRng.Offset(offst, 1) = ""
      TotalRng.Offset(offst).Value = TotalRng.Offset(offst, 1).Value
      offst = offst + 1
    Loop
  End If
Next sht
End Sub

 

[snoopyhr]

Just thought maybe a none macro and also independent of text on the last row will be an interesting approach to get the result .... You can easily adjust the formula for sheet name reference.

I just wanted to see if it was possible to this without macro , that is all,

Excel Workbook
	A	B	C	D	E	F	G	H	I
2					Jan	Feb	Mar	Apr	May
3	Month	Sales			1451	2572	1973	2013	2264
4	Jan	$22,160.00			1792	2884	1307	2822	2036
5	Feb	$23,787.00			1149	2193	1919	2542	2066
6	Mar	$ 9,276.00			1643	2117	1194	2803	2976
7	Apr	$27,182.00			1835	2614	1780	2111	2803
8	May	$42,224.00			1552	2588	1103	2761	2970
9					1274	2343	$9,276.00	2703	2605
10					1978	2240		2374	2405
11					1483	2161		2081	2300
12					1870	2075		2751	2198
13					1688	$23,787.00		2221	2349
14					1367			$27,182.00	2548
15					1841				2821
16					1237				2020
17					$22,160.00				2897
18									2572
19									2394
20									$42,224.00
Sheet3

 

[poey]

p45cal and snoopy, both of these worked!
Thank you SO VERY MUCH for your help!

VoG, you rock too!

Laura