Thread: How? Move data from Y into multiple X rows - Macro?VBA?

Hello,

I commonly have this problem where I receive a spreadsheet of data and I need to do a VLOOKUP to get a specific categories name and then data from the single date below it. Attached is an example of BEFORE and AFTER. However, I want to without just copying and pasting the data, move the data into the appropriate row. A bit like a pivot table data and then moving each cateogry/header of information into each row.

try this macro

Sub test()
Dim j As Long, k As Long, m As Long, n As Long, r As Range, cfind As Range
Worksheets("sheet1").Activate
Columns("B:D").Delete
Columns("B:D").Insert
j = 6
k = Cells(j, "E").End(xlDown).Row
Set r = Range(Cells(j, "E"), Cells(k, "E"))
Set cfind = r.Cells.Find(what:="Water", lookat:=xlWhole)
m = cfind.Row
Set cfind = r.Cells.Find(what:="sewer", lookat:=xlWhole)
n = cfind.Row
Range("E9:E10").Copy
Range(Cells(m + 1, "B"), Cells(n - 1, "B")).PasteSpecial Transpose:=True
Range(Cells(m + 1, "D"), Cells(n - 1, "D")) = "Water"
m = n
n = Cells(m, "E").End(xlDown).Row
Range(Cells(m + 1, "B"), Cells(n, "B")).PasteSpecial Transpose:=True
Range(Cells(m + 1, "D"), Cells(n, "D")) = "Sewer"
Application.CutCopyMode = False
End Sub

So, I see in your macro you quote specifically "Water" and "Sewer." The true document I have has about 40 different names and groupings. On the sample document, E7 through E10 will vary through the excel document through about 12,000 rows through each different County (E8), City (E9,E10), and Department (E11,E21). Is there a way to write the macro to copy/paste the data in rows E8 to E11/E21 only if there are dates directly below each department. Can it be ran based on the color of the cell since on the export it does export those specific colors which define what the "category" of data is.