Copy-Paste Data

[gl45]

I am a novice and hope somebody is able to help, I searched the forum with no luck.

What I would like to do is copy the data in each corresponding row only if there is a "W" or a "P" in column AU3:AU12.

So if AU12 has a "W" then all the data in row AW12:BI12 is copied to M3:Y3 same sheet,
and if AU3 has a "P" then all the data in row AW3:BI3 is copied to AA3:AM3 same sheet.

The next day whatever place (AU3:AU12) the "W" and "P" are, the correpsoning rows will be copied one row down as M4:Y4 for "W" criteria, and one row down as AA4:AM4 for the "P" criteria.

Thanks.
GL

 

[gl45]

Howard,
scroll to AU.

GL

 

[L. Howard]

Code realigned, should work.

When you said you had drop all the headers, I adjusted the code to AU1:AU10.

Reset code to AU3:AU12.


https://www.dropbox.com/s/t4mwp5sfqbiw1ba/Copy%20of%20Book1%20ReAligned%20Code%20Drop%20Box.xlsm

Howard

 

[gl45]

Howard,
you did an excellent job in solving my Excel problem. One more question for you, what would be the easiest way to use the same macro in the next 7 sheets with identical format as the one you solved. Should give 7 new names to the macro and attach each one to the 7 sheets?
Thank you,
GL

 

[gl45]

Howard,
I create 7 new macro with different names for each sheet and everything works perfectly.
Thank you very much for your help.

Cheers,
GL

 

[L. Howard]

You are welcome, glad to help.

If you want to reduce the number of macros you can use this one to do all of the worksheet from one code.
It goes in a Standard Module.
If you are unaware of a standard module, go to the sheet vb editor and look up at the top left and find INSERT. Click it and in the drop down click on Module.
Paste the code in the here.

You can add or delete sheet to be included or excluded where you see

Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

These are default sheet names. If you rename your sheets then the new name must go here.

Case "Sheet1", "MySheet", "YourSheet", "OurSheet"

as an example.

Regards,
Howard

Sub MyAURangeValuesAllSheets()

 Dim ws As Worksheet
 Dim c As Range
 
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
      Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
      
      For Each c In ws.Range("AU3:AU12")
        
          If c = "W" Then
              c.Offset(0, 16).Resize(1, 13).Copy
              ws.Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
          End If
      
          If c = "P" Then
              c.Offset(0, 16).Resize(1, 13).Copy
              ws.Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
          End If
          
        Next
    End Select
Next ws
End Sub

 

[gl45]

Howard,
sorry to be late in responding. Everything is working great with minimum effort thanks to you.
Again, I greatly appreciate your help.

Best regards,
GL

 

[L. Howard]

Your welcome, glad to hl and glad it working for you.

Regards,
Howard