I have a report that a database exports. The first three columns are
customer specific. If a customer has a single order, then only qty1, part1,
and UM1 is populated. (Columns 4, 5, and 6). If a customer has two items to
order, then qty1, part1, UM1, qty2, part2, and UM2 are all populated.
(Columns 1-9).

I've been asked to change the format of the excel file with a macro. In the
event that qty 2 is populated, (a 2nd item exists for the order) then a new
row will be created below the existing row. The customer data (Col 1, 2, 3)
would be copied, and pasted into col 1,2, and 3 of the new row. Then the qty
2, part2, and UM2 data would be moved to the new row and would populate the
qty1, part1, and UM1 columns (Col 4, 5, and 6).

In otherwords, the report would no longer need columns 7, 8, and 9 because
all information would be captured in columns 1-6.

Sorry for the long winded description, but any help that can be provided
would be appreciated. The difficulty for me came in building something that
would "sense" that qty 2 data is present. Also, the macro needs to sense how
many rows the table has and stop after checking the last row.

Kind Regards and Thank You!!

The code assumes ther are no blanks rows in the data.

Sub MoveItem2()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("D" & RowCount) < "" Then
Rows(RowCount + 1).Insert
Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=Range("A" & (RowCount + 1))
Range("G" & RowCount & ":I" & RowCount).Copy _
Destination:=Range("D" & (RowCount + 1))
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop
End Sub


"zach-18" wrote:

I have a report that a database exports. The first three columns are
customer specific. If a customer has a single order, then only qty1, part1,
and UM1 is populated. (Columns 4, 5, and 6). If a customer has two items to
order, then qty1, part1, UM1, qty2, part2, and UM2 are all populated.
(Columns 1-9).

I've been asked to change the format of the excel file with a macro. In the
event that qty 2 is populated, (a 2nd item exists for the order) then a new
row will be created below the existing row. The customer data (Col 1, 2, 3)
would be copied, and pasted into col 1,2, and 3 of the new row. Then the qty
2, part2, and UM2 data would be moved to the new row and would populate the
qty1, part1, and UM1 columns (Col 4, 5, and 6).

In otherwords, the report would no longer need columns 7, 8, and 9 because
all information would be captured in columns 1-6.

Sorry for the long winded description, but any help that can be provided
would be appreciated. The difficulty for me came in building something that
would "sense" that qty 2 data is present. Also, the macro needs to sense how
many rows the table has and stop after checking the last row.

Kind Regards and Thank You!!

Thanks for your help Joel, this worked great.

I appreciate your assistance.

Kind Regards,
PZ

"Joel" wrote:

The code assumes ther are no blanks rows in the data.

Sub MoveItem2()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("D" & RowCount) < "" Then
Rows(RowCount + 1).Insert
Range("A" & RowCount & ":C" & RowCount).Copy _
Destination:=Range("A" & (RowCount + 1))
Range("G" & RowCount & ":I" & RowCount).Copy _
Destination:=Range("D" & (RowCount + 1))
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop
End Sub


"zach-18" wrote:

I have a report that a database exports. The first three columns are
customer specific. If a customer has a single order, then only qty1, part1,
and UM1 is populated. (Columns 4, 5, and 6). If a customer has two items to
order, then qty1, part1, UM1, qty2, part2, and UM2 are all populated.
(Columns 1-9).

I've been asked to change the format of the excel file with a macro. In the
event that qty 2 is populated, (a 2nd item exists for the order) then a new
row will be created below the existing row. The customer data (Col 1, 2, 3)
would be copied, and pasted into col 1,2, and 3 of the new row. Then the qty
2, part2, and UM2 data would be moved to the new row and would populate the
qty1, part1, and UM1 columns (Col 4, 5, and 6).

In otherwords, the report would no longer need columns 7, 8, and 9 because
all information would be captured in columns 1-6.

Sorry for the long winded description, but any help that can be provided
would be appreciated. The difficulty for me came in building something that
would "sense" that qty 2 data is present. Also, the macro needs to sense how
many rows the table has and stop after checking the last row.

Kind Regards and Thank You!!