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!
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!