Find variable in Col B, Copy Paste Values in Cn:Tn to Row below
 

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row below
 

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row below
 

Hi,
Try the following code.

''' -------------------------------
Sub test()
Dim c As Range
Dim result As Range
Dim firstAddress As String


''' find all FC cells
With ActiveSheet.Range("B:B") ''' <<<<< Range to search
Set c = .Find("FC", LookIn:=xlValues, lookat:=xlWhole) ''' <<<<< what to
search for

If Not c Is Nothing Then
firstAddress = c.Address
Do
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

''' copy/paste
If Not result Is Nothing Then ''' if some cells were found
For Each c In result.Cells
Set c = Application.Intersect(c.EntireRow _
, c.Parent.Range("C:T"))
c.Copy c.Offset(1, 0)
Next
End If

End Sub
''' -------------------------------

Potential issue: in the copy/paste section, if 2 FC cells follow each other
then copy/pasting the 1st one will overwrite the second FC row data therefore
the second copy/paste will carry over on the 3rd row the data of the 1st FC
row. Now, it is possible that this cannot happen in your situation, say for
example that your sheet is organized in such a way that no 2 FC can follow
each other. If this can happen however, you'll need to rewrite the Copy/Paste
section to start copying/pasting the last row and going up.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row belo
 

Did you intend to include a suggestion?

"sebastienm" wrote:


--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row belo
 

Thanks, that works well, the only issue I need to address is the formatting.
I would like to only paste the values.

Thanks again.

"sebastienm" wrote:

Hi,
Try the following code.

''' -------------------------------
Sub test()
Dim c As Range
Dim result As Range
Dim firstAddress As String


''' find all FC cells
With ActiveSheet.Range("B:B") ''' <<<<< Range to search
Set c = .Find("FC", LookIn:=xlValues, lookat:=xlWhole) ''' <<<<< what to
search for

If Not c Is Nothing Then
firstAddress = c.Address
Do
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

''' copy/paste
If Not result Is Nothing Then ''' if some cells were found
For Each c In result.Cells
Set c = Application.Intersect(c.EntireRow _
, c.Parent.Range("C:T"))
c.Copy c.Offset(1, 0)
Next
End If

End Sub
''' -------------------------------

Potential issue: in the copy/paste section, if 2 FC cells follow each other
then copy/pasting the 1st one will overwrite the second FC row data therefore
the second copy/paste will carry over on the 3rd row the data of the 1st FC
row. Now, it is possible that this cannot happen in your situation, say for
example that your sheet is organized in such a way that no 2 FC can follow
each other. If this can happen however, you'll need to rewrite the Copy/Paste
section to start copying/pasting the last row and going up.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row belo
 

For values only, instead of
c.Copy c.Offset(1, 0)
try something like:
c.Copy
c.Offset(1, 0).PasteSpecial xlPasteValues

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

Thanks, that works well, the only issue I need to address is the formatting.
I would like to only paste the values.

Thanks again.

"sebastienm" wrote:

Hi,
Try the following code.

''' -------------------------------
Sub test()
Dim c As Range
Dim result As Range
Dim firstAddress As String


''' find all FC cells
With ActiveSheet.Range("B:B") ''' <<<<< Range to search
Set c = .Find("FC", LookIn:=xlValues, lookat:=xlWhole) ''' <<<<< what to
search for

If Not c Is Nothing Then
firstAddress = c.Address
Do
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

''' copy/paste
If Not result Is Nothing Then ''' if some cells were found
For Each c In result.Cells
Set c = Application.Intersect(c.EntireRow _
, c.Parent.Range("C:T"))
c.Copy c.Offset(1, 0)
Next
End If

End Sub
''' -------------------------------

Potential issue: in the copy/paste section, if 2 FC cells follow each other
then copy/pasting the 1st one will overwrite the second FC row data therefore
the second copy/paste will carry over on the 3rd row the data of the 1st FC
row. Now, it is possible that this cannot happen in your situation, say for
example that your sheet is organized in such a way that no 2 FC can follow
each other. If this can happen however, you'll need to rewrite the Copy/Paste
section to start copying/pasting the last row and going up.

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Chris" wrote:

I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.