Code to find a word in column D and copy data from column D to column J and paste to cell D1

[mompy76]

Hi guys/girls

I'm needing some help please. I am self taught macro writer and I am having issues trying to figure out how to write this macro. Essentially, this is what I am trying to do:-

- use a word filter on column D to find the word "Total" - there will be 8 rows in total
- copy the found content from Column D to Column J for these 8 rows (Note - these rows will be on a different line in each report - which is where i am coming unstuck)
- paste these 8 rows of data at cell D1

If you know of a better way to do this (other than using a word filter), I am happy for suggestions.

Thanks in advance!

 

[NotoriousPopol]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

Do you want to keep the data the same, and just make a copy of it starting in the Column J?

I would use an Array, then go through all the data and put what you want in it, then copy the content of the array where you want.

Something like :

Dim arr(1 To 8) As Variant
Dim i As Integer, iRow As Integer

i=1

For iRow = 1 To Range("D10000").End(xlUp).Row
    If Cells(iRow,4)="Total" Then
        arr(i)=Range(Cells(iRow,4), Cells(iRow, 6)).Value 'change the number of columns
        i = i +1
    End If
Next

Range("J1:L8")=arr 'copy the data where you want NEED THE SAME SIZE

 

[mompy76]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

Hi NotoriousPopol

Thanks for your response.

Sorry for the confusion ... thats not exactly what I am after.

I need to find the rows in column D that contain "Total". (I know that there are 8 in total throughout the raw data report)

Copy the rows containing "Total" from Column D to Column J (inclusive)

Then paste these rows (Columns D to J) at Cell D1 - so essentially I am moving the data from cells Dxxx to Jxxx to cells D1 to J8

Hope that makes sense.

 

[inactiveuserps07]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

mompy, I'm thinking there's some confusion in your wording. Let me see if I've got it right:

Your data takes up many rows lower down, and each entry of data runs from D all the way across to J (D E F G H I J).

You have eight rows at the top that are NOT included in your data, into which you want to copy the eight records where the word "Total" is found in Column D.

In other words, Row 1 will contain the first instance of "Total" found in Column D within the lower area (Row 9 and below) of your worksheet along with the six cells to its right. Row 2 will contain the second instance of "Total" found in the lower records, etc.

Is this correct?

 

[Rick Rothstein]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub MoveTotalsToFirstEightRows()
  With Range("D9:J" & Columns("D:J").Find("*", , xlFormulas, , xlRows, xlPrevious).Row)
    .Replace "Total", "#N/A", xlWhole, , False, , False, False
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Columns("D:J")).Copy Range("D1")
  End With
  Columns("D").Replace "#N/A", "Total", xlWhole
End Sub[/td]
[/tr]
[/table]

 

[inactiveuserps07]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

mompy, also ... is your lower original data in D:J formatted as a table? (It should be.) What is the table name? And what are the header names for each of the seven columns? That would make a formula approach a lot easier (though it looks like Rick's already whipped up a VBA solution for you, which may render my additional questions here moot).

 

[mompy76]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MoveTotalsToFirstEightRows()
  With Range("D9:J" & Columns("D:J").Find("*", , xlFormulas, , xlRows, xlPrevious).Row)
    .Replace "Total", "#N/A", xlWhole, , False, , False, False
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Columns("D:J")).Copy Range("D1")
  End With
  Columns("D").Replace "#N/A", "Total", xlWhole
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick

Thanks for the above. I have tried it out, but it is coming up with a debug error on the line

Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Columns("D:J")).Copy Range("D1")

Can you offer an suggestions?

 

[mompy76]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

mompy, also ... is your lower original data in D:J formatted as a table? (It should be.) What is the table name? And what are the header names for each of the seven columns? That would make a formula approach a lot easier (though it looks like Rick's already whipped up a VBA solution for you, which may render my additional questions here moot).

Hi Erik

In response to your first reply - yes, that is correct.

In response to the above, no, nothing is formatted as a table. The file this macro is being used on is an excel data dump from a program - and I am formatting it to incorporate into another file. Unfortunately, there are no "Header names" for the columns - but if need be, I am happy to change that and name them A, B, C, D, etc, if that assists.

Below is a snippet of the raw data excel file. The highlighted area below is what I am wanting to find using the word "Total" and then copy to cell D1 (so essentially the data will copied to cells D1 to J8).


-- removed inline image ---


Apologies if my wording is confusing.

Thanks

 

[mompy76]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

-- removed inline image ---

 

[inactiveuserps07]

Re: Help - Code to find a word in column D and copy data from column D to column J and paste to cell D1

mompy76, none of your images is showing, just the tiny generic image placeholder.

However ...

Is Column C currently blank (i.e., could it be used as a helper cell)? Or, if not, is either A or B empty and available as a helper? If not, what is the nearest column that could be used as a helper column?

And I assume that at least Rows 1:8 are currently clear, yes?

If you can work out why the images aren't showing, that would also help.