Changing ranges of current code

rhudgins,

Would the macro loop every 25 columns from G if A60=1? Or would it loop every 25 columns from E still, it would just start at column G instead of E?

~tigeravatar

The code will copy as values the range E59:E93 to G59:G93 if A60=1. Then the code will skip 25 columns and copy as values the range AD59:AD93 to AF59:AF93 if Z60=1.... continue this sequence in a loop as more data will be added to the worksheet over time

rhudgins,

Change this line of code:

Set rngConvert = ActiveSheet.Range("E59:E93").Offset(0, CurrentColumn)

To be this instead:

Set rngConvert = ActiveSheet.Range("E59:G93").Offset(0, CurrentColumn)

Hope that helps,
~tigeravatar

I must not have been clear in my previous post. I meant copy the range E59:E93 and paste it as values to the range G59:G93 only if A60=1...... ect.....

Is this clearer? Sorry for the confusion.

rhudgins,

Ah, I think I see what you mean now. Try this macro:

Private Sub PasteValuesRediLinks()
    
    Dim rngConvert As Range
    Dim rngCheck As Range
    Dim CurrentColumn As Long:  CurrentColumn = 0
    Dim LastColumn As Long:     LastColumn = ActiveSheet.Cells(59, Columns.Count).End(xlToLeft).Column
    While CurrentColumn <= LastColumn
        Set rngConvert = ActiveSheet.Range("E59:E93").Offset(0, CurrentColumn)
        Set rngCheck = ActiveSheet.Range("A60").Offset(0, CurrentColumn)
        rngConvert.Value = rngConvert.Value
        rngConvert.Font.ColorIndex = xlAutomatic
        rngConvert.Font.TintAndShade = 0
        If rngCheck.Value = 1 Then
            rngConvert.Copy
            rngConvert.Offset(0, 2).PasteSpecial xlPasteAll, Transpose:=False
        End If
        CurrentColumn = CurrentColumn + 25
    Wend
    
End Sub

Hope that helps,
~tigeravatar

Thank you. This works perfectly.