Copy-Paste Data

G

gl45

Board Regular
Joined
Jul 16, 2005
Messages
65
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Give this a try in the sheet module.
Put a header in M2 and AA2

You could also name the range UA3:AU12 to suit, I named it MyAURange, and use it in the code where you see the red line.

Regards,
Howard

Code: 
Option Explicit

Sub MyAURange()

Dim c As Range

[COLOR=#ff0000]'For Each c In Range("MyAURange")
[/COLOR]For Each c In Range("UA3:AU12")

  If c = "W" Then
    c.Offset(0, 2).Resize(1, 13).Copy Cells(Rows.Count, "M").End(xlUp).Offset(1, 0)
  End If
  
  If c = "P" Then
    c.Offset(0, 2).Resize(1, 13).Copy Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0)
  End If
  
Next
End Sub
 
Upvote 0
Howard,
thanks for your help. The sheet name is "1F", should I replace "My AURange"with "1F"?
At the moment the macro is not working.
Thanks again,
GL
 
Upvote 0
The sheet name does not matter.

You can delete the 'For Each c In Range("MyAURange") use the.. For Each c In Range("UA3:AU12").

Does that work for you?

I am assuming the W and the P are single letters somewhere in the Range("UA3:AU12").

Also, you show upper case W and P. The code is looking for an upper case W and P, so if you have lower case it ignores it.

If that is a problem, then enter this right below the Option Explicit in the sheet module where you have the code Option Compare Text

Case is not sensitive now.

Howard
 
Upvote 0
Howard,
Almost there. The macro does work but it gives #REF!, I think the reason is that the rows to be copied do have formula.
Would be necessary to just copy the rows as value?
Thanks again,
GL
 
Upvote 0
Sorry, major typo in sub range

Rich (BB code): 
For Each c In Range("UA3:AU12")

Should be:

Range("AU3:AU12")

Try this with the paste special values.

Rich (BB code): 
Option Explicit
Option Compare Text

Sub MyAURange()

Dim c As Range
Application.ScreenUpdating = False

For Each c In Range("AU3:AU12")

  If c = "W" Then
    c.Offset(0, 2).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, 2).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
 
Upvote 0
Howard,
sorry to bother you again. Would be possible to change the rows to be copied from AW12:BI12 to BK12:BW12, and from AW3:BI3 to BK3:BW3.
Also can the same macro be copied to other contiguous 8 sheets with identical formats?
Thanks again,
GL
 
Upvote 0
Howard,
the revised macro still gives #REF! error.
I appreciate your help.
Thanks, GL
 
Upvote 0
Can you post a link to an example workbook detailing exactly what need to be copied from where and to where.

Don't see much problem with the multiple sheet and a macro for each, We can make it work for the sheet it is in easy enough.

The ranges are easy enough to adjust. The #REF is puzzling tho.

I don't need eight sheets, two or three will be plenty to get us going. Past that, it is just busy work.

Howard
 
Upvote 0
Howard,
I hope I did correctly
Excel 2007
AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW
2 LAST LAST 2 LAST 3 LAST 4 2 of 3 2 of 4 3 of 4 LAST 2 LAST 3 LAST 4 EVER BEST EVER 2 EVER LAST LAST 2 LAST 3 LAST 4 2 of 3 2 of 4 3 of 4 LAST 2 LAST 3 LAST 4 EVER BEST EVER 2 EVER
3 P 1 89 89 89 92 88 91 89 88 84 86 98 96 97 6 8 9 5 9 6 6 7 9 9 4 4 4
4 2 95 95 97 99 96 98 97 94 95 96 99 97 98 3 3 2 1 2 2 2 3 2 2 2 3 3
5 3 84 90 91 91 91 91 89 87 88 88 93 92 93 9 7 6 7 4 6 7 8 5 6 6 6 6
6 4 99 99 99 99 99 99 97 96 97 96 99 99 99 1 1 1 1 1 1 1 1 1 1 2 1 2
7 5 97 97 97 97 95 95 96 95 93 92 105 99 102 2 2 2 3 3 3 3 2 3 3 1 1 1
8 6 86 86 92 92 89 92 90 86 88 89 92 91 92 7 9 5 5 7 5 5 9 6 4 7 8 8
9 7 86 94 94 94 90 93 91 90 88 89 96 94 95 7 4 4 4 5 4 4 4 7 4 5 5 5
10 8 91 91 91 91 90 90 89 89 89 88 91 89 90 4 5 6 7 5 8 8 6 4 6 9 9 9
11 9
12 W 10 91 91 91 91 89 89 87 89 87 87 92 92 92 4 5 6 7 7 9 9 5 8 8 7 6 7
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Lookin for VBA/Macro to Copy/Paste only cells in corresponding row.
2 3
Replies
20
Views
1K
Alex Blakenburg
A
WildBurrow
  • Question
Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up
Replies
0
Views
109
WildBurrow
WildBurrow
B
  • Solved
Copying a row of data from another sheet and pasting it into another sheet into the correct column headings
Replies
8
Views
388
kevin9999
K
J
  • Question
Copying and splitting data between sheets
Replies
3
Views
196
Jumparound
J
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
257
KRice
K

Forum statistics

Threads
1,214,805
Messages
6,121,665
Members
449,045
Latest member
Marcus05

Share this page

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