Hi,
Theres four columns of data A:D.
If Column A has "Total Spend ", copy header A1:D1 and paste it on the second
row below Total Spend. So for example,

A1 B1 C1 D1
Vendor Category Option Spend
ABC
IBM
MAC
Total Spend
(Blank Row)
Automatically Paste Vendor Category Option Spend
Sample Vendor1
Sample Vendor2
Sample Vendor3
Total Spend

Vendor Category Option Spend



Any help is appreciated. Thank you

select yr data and use the following macro:

Sub kopiuj()
For Each cell in Selection
If cell.Text = "Total Spend" Then
Range("A1:D1").Copy cell.Offset(2,0)
End If
Next cell
End Sub


I hope you don't store any data in any of the cells that 2 rows below
"Total Spend"

Should do the trick unless there's more to it. Hope it helps!

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value = "Total Spend" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

"dd" wrote:

Hi,
Theres four columns of data A:D.
If Column A has "Total Spend ", copy header A1:D1 and paste it on the second
row below Total Spend. So for example,

A1 B1 C1 D1
Vendor Category Option Spend
ABC
IBM
MAC
Total Spend
(Blank Row)
Automatically Paste Vendor Category Option Spend
Sample Vendor1
Sample Vendor2
Sample Vendor3
Total Spend

Vendor Category Option Spend



Any help is appreciated. Thank you

Thanks StumpedAgain!
I have two more questions.

Is there a way to changed the "Total Spend" to Total Spend - anything?
Example, if the word was Total Spend - Apple or Total Spend - Cars?

Also is it possible to subtotal the column C for each cluster before the
header row? For example,

Vendor Category Option Spend
ABC Computer 50 1000
IBM Computer 3000
MAC Computer 9 2000
Total Spend - Computer 59 6000

Vendor Category Option Spend
1 Any 5 100
2 Any 0
3 Any 1 10
Total Spend - Any 6 110






"StumpedAgain" wrote:

Should do the trick unless there's more to it. Hope it helps!

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value = "Total Spend" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

"dd" wrote:

Hi,
Theres four columns of data A:D.
If Column A has "Total Spend ", copy header A1:D1 and paste it on the second
row below Total Spend. So for example,

A1 B1 C1 D1
Vendor Category Option Spend
ABC
IBM
MAC
Total Spend
(Blank Row)
Automatically Paste Vendor Category Option Spend
Sample Vendor1
Sample Vendor2
Sample Vendor3
Total Spend

Vendor Category Option Spend



Any help is appreciated. Thank you

The following covers both questoins. (mind the wrapping) I had to use like
and a "*" for your first request. This program allows for different number
of rows in each section. Hope it helps! Let me know if there's anything
else.

-SA

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value Like "Total Spend*" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
ActiveCell.End(xlDown).Offset(0, 2) =
Application.Sum(Range(ActiveCell.Offset(1, 2),
ActiveCell.End(xlDown).Offset(-1, 2)))
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

"dd" wrote:

Thanks StumpedAgain!
I have two more questions.

Is there a way to changed the "Total Spend" to Total Spend - anything?
Example, if the word was Total Spend - Apple or Total Spend - Cars?

Also is it possible to subtotal the column C for each cluster before the
header row? For example,

Vendor Category Option Spend
ABC Computer 50 1000
IBM Computer 3000
MAC Computer 9 2000
Total Spend - Computer 59 6000

Vendor Category Option Spend
1 Any 5 100
2 Any 0
3 Any 1 10
Total Spend - Any 6 110






"StumpedAgain" wrote:

Should do the trick unless there's more to it. Hope it helps!

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value = "Total Spend" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

"dd" wrote:

Hi,
Theres four columns of data A:D.
If Column A has "Total Spend ", copy header A1:D1 and paste it on the second
row below Total Spend. So for example,

A1 B1 C1 D1
Vendor Category Option Spend
ABC
IBM
MAC
Total Spend
(Blank Row)
Automatically Paste Vendor Category Option Spend
Sample Vendor1
Sample Vendor2
Sample Vendor3
Total Spend

Vendor Category Option Spend



Any help is appreciated. Thank you

Thanks for the fast response. You solved my first question but I made a
mistake in asking my second question.
Under Column C, basically says Yes or the cell is blank and column D has the
spend amount. What I wanted to do was to subtotal the Spend amount in Column
D when there is Yes in Col C and the subtotal for the Column C would be
displayed under the total spend row in Column D. I appologize if this sounds
confusing. Thanks so much.

Vendor Category Option Spend
ABC Computer Yes 1000
IBM Computer 3000
MAC Computer Yes 2000
Total Spend - Computer 6000
3000
(so $3000 is displayed Col D)



"StumpedAgain" wrote:

The following covers both questoins. (mind the wrapping) I had to use like
and a "*" for your first request. This program allows for different number
of rows in each section. Hope it helps! Let me know if there's anything
else.

-SA

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value Like "Total Spend*" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
ActiveCell.End(xlDown).Offset(0, 2) =
Application.Sum(Range(ActiveCell.Offset(1, 2),
ActiveCell.End(xlDown).Offset(-1, 2)))
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

What is this a test? These questions keep evolving and getting more and more
involved! ;)

The following should do the trick. I had to change the exit point and
define some variables. Let me know if there's any more changes. (mind the
wrapping again)

-SA

Option Explicit
Sub Copy_Headers()

Dim i, m As Integer, r As Long

Range("A1").Select
r = 0

Do
If ActiveCell.Offset(1, 0) = "" Then Exit Do
With ActiveCell
m = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
End With
If ActiveCell.End(xlDown).Value Like "Total Spend*" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
ActiveCell.End(xlDown).Offset(0, 3) =
Application.Sum(Range(ActiveCell.Offset(1, 3),
ActiveCell.End(xlDown).Offset(-1, 3)))
For i = 0 To m
If ActiveCell.Offset(i, 2).Value Like "Yes" Then r = r +
ActiveCell.Offset(i, 3).Value
Next i
ActiveCell.End(xlDown).Offset(1, 3) = r
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub

"dd" wrote:

Thanks for the fast response. You solved my first question but I made a
mistake in asking my second question.
Under Column C, basically says Yes or the cell is blank and column D has the
spend amount. What I wanted to do was to subtotal the Spend amount in Column
D when there is Yes in Col C and the subtotal for the Column C would be
displayed under the total spend row in Column D. I appologize if this sounds
confusing. Thanks so much.

Vendor Category Option Spend
ABC Computer Yes 1000
IBM Computer 3000
MAC Computer Yes 2000
Total Spend - Computer 6000
3000
(so $3000 is displayed Col D)



"StumpedAgain" wrote:

The following covers both questoins. (mind the wrapping) I had to use like
and a "*" for your first request. This program allows for different number
of rows in each section. Hope it helps! Let me know if there's anything
else.

-SA

Option Explicit
Sub Copy_Headers()

Range("A1").Select

Do
If ActiveCell.End(xlDown).Value Like "Total Spend*" Then
Range("A1:D1").Copy ActiveCell.End(xlDown).Offset(2, 0)
ActiveCell.End(xlDown).Offset(0, 2) =
Application.Sum(Range(ActiveCell.Offset(1, 2),
ActiveCell.End(xlDown).Offset(-1, 2)))
Else: Exit Do
End If

ActiveCell.End(xlDown).Offset(2, 0).Select

Loop

End Sub