Copy Paste macro

[lesliewheeler]

I require a macro or a shortcut that will help me Copy cells from range L3:L41 and paste special values in L3:L41
The values picked from column L should only replace blank cells in column P. Incase there is a pre existing value in the range p341 then it skips that cell completely and original value on that cell remains as is without any change.

I would want this to also simultaneously happen for
M3:M41 to Q3:Q41
N3:N41 to R3:R41
O3:O41 to S3:S41


PLs help urgently I am desparate

 

[tygrrboi]

Re: Urgent!!--Need help with Copy Paste macro

try this.

Sub FillBlanks()


Dim C As Range, PasteRange As Range
Dim i As Integer


Set PasteRange = Range("P3:P41")




For i = 1 To 4 'runs 4 times for P, Q, R, S
    
    For Each C In PasteRange
        If C.Value = vbNullString Then
            C.Value = C.Offset(0, -4).Value
        End If
    Next C
    Set PasteRange = PasteRange.Offset(0, 1)
Next i


End Sub

 

[lesliewheeler]

Re: Urgent!!--Need help with Copy Paste macro

try this.

Sub FillBlanks()


Dim C As Range, PasteRange As Range
Dim i As Integer


Set PasteRange = Range("P3:P41")




For i = 1 To 4 'runs 4 times for P, Q, R, S
    
    For Each C In PasteRange
        If C.Value = vbNullString Then
            C.Value = C.Offset(0, -4).Value
        End If
    Next C
    Set PasteRange = PasteRange.Offset(0, 1)
Next i


End Sub

HI Thanks for the quick response.

However this is giving error Type Mismatch when I make changes in the sheet and re run the macro.
Pls help

 

[tygrrboi]

Re: Urgent!!--Need help with Copy Paste macro

It works the first time but gives an error the second time? What types of changes are you making? And if you troubleshoot the error, what section of the code is showing the error?

 

[lesliewheeler]

Re: Urgent!!--Need help with Copy Paste macro

Hi ive been able to solve it..when the macro was pasting the values was also pasting the the error as well, so when it was overwriting it was giving type mismatch.
All i did was to extend this by adding find and replace errorr text with blank and it works like a charm.

Thank you so much for your help

 

[lesliewheeler]

Re: Urgent!!--Need help with Copy Paste macro

Hi ive been able to solve it..when the macro was pasting the values was also pasting the the error as well, so when it was overwriting it was giving type mismatch.
All i did was to extend this by adding find and replace errorr text with blank and it works like a charm.

Thank you so much for your help