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,
I omitted the headings. AU column has the "W" and "P", column AV has 10 rows, need to copy BK: BW range to M:Y when "W" is present in AU column and
copy BK:BW range to AA:AM range when "P" are present.
Also there are formulas in the cells to be copied like: =IF(AW3="","",RANK(AW3,$AW$3:$AW$12)).
Again many thanks and sorry for the illegible previous post.
GL





Excel 2007
AU AV BK BL BM BN BO BP BQ BR BS BT BU BV BW
3 P 1 6 8 9 5 9 6 6 7 9 9 4 4 4
4 2 3 3 2 1 2 2 2 3 2 2 2 3 3
5 3 9 7 6 7 4 6 7 8 5 6 6 6 6
6 4 1 1 1 1 1 1 1 1 1 1 2 1 2
7 5 2 2 2 3 3 3 3 2 3 3 1 1 1
8 6 7 9 5 5 7 5 5 9 6 4 7 8 8
9 7 7 4 4 4 5 4 4 4 7 4 5 5 5
10 8 4 5 6 7 5 8 8 6 4 6 9 9 9
11 9
12 W 10 4 5 6 7 7 9 9 5 8 8 7 6 7
Sheet1

 

[L. Howard]

Also there are formulas in the cells to be copied like: =IF(AW3="","",RANK(AW3,$AW$3:$AW$12)).

Do you want to copy the formulas or the values?

Howard

 

[gl45]

Howard,
Just the values.
Thanks

GL

 

[L. Howard]

No headers on anything, but you will have one blank row at the very top, row 1 of the copied material.

Howard

Option Explicit
Option Compare Text

Sub MyAURangeValues()

Dim c As Range
Application.ScreenUpdating = False

For Each c In Range("AU1:AU10")

  If c = "W" Then
    c.Offset(0, 17).Resize(1, 13).Copy
    Cells(Rows.Count, "M").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
  End If
  
  If c = "P" Then
    c.Offset(0, 17).Resize(1, 13).Copy
    Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
  End If
  
Next

Range("AU3").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[gl45]

Howard,
macro is copying in the right place but is not copy the values, is copying the formula.
Also is copying the AW:BI range rather than BK:BW range.
Thanks for your patience.
GL

 

[L. Howard]

Looks like I had the offset wrong, notice this code is 16, last one was 17.

c.Offset(0, 16).Resize(1, 13).Copy

But it should still have copied the values and not the formulas.

Try this code and let me know what happens.

Howard

Option Explicit
Option Compare Text

Sub MyAURangeValues()

Dim c As Range
Application.ScreenUpdating = False

For Each c In Range("AU1:AU10")

  If c = "W" Then
    c.Offset(0, 16).Resize(1, 13).Copy
    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
    Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
  End If
  
Next

Range("AU3").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[gl45]

Howard,
Sorry, no luck.

GL

 

[L. Howard]

Here is a link to my example.

Seems to me it does what you want,
Note all the cells in the copy range have simple formulas.
Once copied they are values only. No formulas in the pasted ranges.
Click the button and check results.

https://www.dropbox.com/s/8x9547vczau03t1/Book5%20Copy%20Values%20Example%20Drop%20Box.xlsm

Howard

 

[gl45]

Howard,
I hope the link will tell the story.

https://www.dropbox.com/s/v4molvmbsw7tja3/Book1.xlsx

GL

 

[L. Howard]

All I got was an empty workbook...???

Howard