PDA

View Full Version : Copy rows with specific values and paste within the same worksheet



thomasccw
04-20-2018, 12:08 AM
Dear experts,

I need a macro that does the following for every worksheet excluding one (named "Master"):

1. Delete columns C, E, K, L.

2. For the new Column D titled "Span", there are a total of 4 possible variables - "1", "2", "3", and "4". For each variable, I would like to copy its row into another part of the worksheet.

For example,
- all rows with "1" copied to rows 20-28, with row 29 as such: the last value of anything entered in A20:A28 in A29, B20:B28 in B29, C20:C28 in C29, D20: D28 in D29, I20:I28 in I29, and the average for E20:E28 in E29, F20:F28 in F29, G20:G28 in G29, H20:H28 in H29.

- all rows with "2" copied to rows 30-38, with row 39 (same as above, but update the rows to 30-38)
- all rows with "3" copied to rows 40-48, with row 49 (same as above, but update the rows to 40-48)
- all rows with "4" copied to rows 50-58, with row 59 (same as above, but update the rows to 50-58)

-
I'm currently using:

If cell.Value = "1" Then
matchRow = cell.Row
Rows(matchRow & ":" & matchRow).Select
Selection.Copy


But I have no idea how to make it paste to the specific rows without overwriting, say anything with "1".

Any help is appreciated, thank you!

offthelip
04-23-2018, 04:17 PM
it is not clear what you are trying to do, but this code shows you how to copy the row (match row ) to a destination row, once you have copied one row you can increment "destinationrow" for the next row


destinationrow = 20

'If cell.Value = "1" Then
matchRow = 1
With ActiveSheet
.Rows(matchRow).Copy .Rows(destinationrow)
End With
End Sub



If you want to rearrange the data in the same worksheet one way of doing that is to load the entire sheet into a variant array, then delete the data onthe sheet and then write it back where you want it