Macro Copy and Paste Value and formatting in multiple same cells when condition is met.

S

Simonjhp

New Member
Joined
May 24, 2024
Messages
3
Office Version
  1. 2016
Platform
  1. MacOS
Hi all,
I am trying to create a macro to copy and paste Values and formatting In Columns M, O, AA:AE when column AF = "Y".
I have tried the following but i doesn't work.

I'd appreciate any help.

Thanks

Option Explicit
Sub PasteContitionalSpecial()
Dim lastrow As Long, r As Long
With ThisWorkbook.ActiveSheet
lastrow = .Range("AF" & .Rows.Count).End(xlUp).Row
For r = 2 To lastrow
If .Range("AF" & r).Value = "Y" Then
.Range("M" & r).Value = .Range("M" & r).Value
End If
Next r
End With
End Sub

I'm wondering if some variation of the following could work but everything I try fails.
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 2/22/2020 11:42:10 AM EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 31 Then
Dim Lastrow As Long
Lastrow = Sheets("Open").Cells(Rows.Count, 13).End(xlUp).Row + 1
Dim ans As Long
ans = Target.Row
If Target.Value = "Y" Then
Rows(ans).Copy Sheets("open").Rows(Lastrow)

End If
End If
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi,
not sure if its the cause of your issue, but it could be one of them:

VBA Code: 
.Range("M" & r).Value = .Range("M" & r).Value

this is copying the data in M&r back to M&r if AF&r = "Y" (eg. the same location ?)

its not clear for us where / what you want to copy out of the columns you mentioned above ... ?

Rob
 
Upvote 0
RobP said:
Hi,
not sure if its the cause of your issue, but it could be one of them:

VBA Code: 
.Range("M" & r).Value = .Range("M" & r).Value

this is copying the data in M&r back to M&r if AF&r = "Y" (eg. the same location ?)

its not clear for us where / what you want to copy out of the columns you mentioned above ... ?

Rob
Click to expand...
 
Upvote 0
Hi Rob,
What I am trying to do is: When I mark column AF as "Y" on sheet 1 I want to copy and paste the Value and formatting in column M on sheet 1. (So change the formula In column M to the value.) This happens on multiple rows.
Does that make sense? Sorry I'm not very good at Macros.
 
Upvote 0
So your code is thus, which I took below, and changed columns for ease of showing results on my side.

VBA Code: 
Option Explicit
Sub PasteContitionalSpecial()
Dim lastrow As Long, r As Long
With ThisWorkbook.ActiveSheet
lastrow = .Range("AF" & .Rows.Count).End(xlUp).Row
For r = 2 To lastrow
If .Range("AF" & r).Value = "Y" Then
.Range("M" & r).Value = .Range("M" & r).Value
End If
Next r
End With
End Sub


My code, executed and did what you expected... that is, replace formulas in my Column D with the actual values instead.
VBA Code: 
Sub test()

Dim lastrow As Long, r As Long

With ThisWorkbook.ActiveSheet

    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row  'change "B" to "AF"
    For r = 2 To lastrow
        If .Range("B" & r).Value = "Y" Then 'change "B" to "AF"
            .Range("D" & r).Value = .Range("D" & r).Value  'change both "D" to "M" in your case
        End If
    Next r
    
End With

End Sub

Results : you can see some cells are values, some still have formulas where there is no "Y" ?

Book1
ABCD
1
23Y6
35Y10
4714
5918
64Y8
7612
8816
Sheet1
Cell Formulas
RangeFormula
D4:D5,D7:D8D4=A4*2


Let me know if my understanding is incorrect ..

cheers
Rob
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
problem with autoincrement
Replies
1
Views
78
rodanny
R
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
252
Edgar_
Edgar_
M
  • Question
Adding a link in email macro using a cell value
Replies
3
Views
141
KOKOSEK
KOKOSEK
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
223
Tupe77
T
T
  • Question
Slow macro
Replies
5
Views
114
topi1
T

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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