Copy rows with specific values and paste within the same worksheet

thomasccw

New Member
Joined
Apr 11, 2018
Messages
4
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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi bud,

Regarding the 1st part:

Code:
Sub Part_1()

Dim Master              As Worksheet

'1 - Deleteing C,E,K,L
    
    'Named sheet to keep it easier to recall
    Set Master = Worksheets("Master")
    Master.Range("C:C,E:E,K:K,L:L").Delete
    
End sub

regarding the 2nd part... could i perhaps see a sample of the data? Is your data from 1-20?
Sorry having hard time imagining it.
 
Upvote 0
Hi DrShadow,

Thanks for the response, I want the columns deleted on all sheets except "Master".

Anyway, for the second part, the data looks like this:

SubIDAPPGameSpanTime_In_SpanCorrect_TrialsTotal_TrialsCorrect_Trials_RTDays
103NumRS110.11s1315133
103NumRS211.11s2123143
103NumRS312.11s2729154
103NumRS313.11s2021165
103NumRS414.11s2528176

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Crosspost http://www.vbaexpress.com/forum/sho...same-worksheet&p=379294&highlight=#post379294

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top