Copy/Paste Values from Variable Range to Corresponding Variable Range

Your sample sheet doesn't have a single named range it it. What happened to them?

They aren't really necessary. You could logically have the macro simply copy B19:BXXXX (where XXXX is the bottom of the data in that columm) to the other sheet in the column where Export-row20 = Projections-Row6.

It would do that for every value of 1 in Export-Row7.

Unanswered from post #2:

Originally Posted by jbeaucaire

They aren't really necessary. You could logically have the macro simply copy B19:BXXXX (where XXXX is the bottom of the data in that columm) to the other sheet in the column where Export-row20 = Projections-Row6.

It would do that for every value of 1 in Export-Row7.

What do you think?

This macro will work without any of the named ranges needing to be present. It uses the key text strings in column A to spot things like:

Row with flags to check (only transfer columns with a numeric value other than 1)
Row with dates
Top row of your data to transfer
Bottom of the data to transfer

Then it spots the row with dates on the Projections sheet

Then it transfers the columns from Export Draws to Projections matching on those dates.

Option Explicit

Sub UpdateProjections()
Dim FR As Long, LR As Long, DateRw As Long, DateRwP As Long
Dim Flag As Range, Dest As Long, FlagRw As Long

Application.ScreenUpdating = False                          'speed things up
With Sheets("Export Draws")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row            'last row with data
    FR = .Range("A:A").Find("PROJECT STATUS", _
            LookIn:=xlValues, LookAt:=xlWhole).Row          'First row, top of data to copy
    FlagRw = .Range("A:A").Find("Total Export Check", _
            LookIn:=xlValues, LookAt:=xlPart).Row           'row with values 0-39
    DateRw = .Range("A:A").Find("SOURCES", _
            LookIn:=xlValues, LookAt:=xlWhole).Row          'row with key dates
    DateRwP = Sheets("Projections").Range("A:A").Find("SOURCES", _
            LookIn:=xlValues, LookAt:=xlWhole).Row          'row with key dates on Projections sheet
            
    
    For Each Flag In .Range("B" & FlagRw, .Cells(FlagRw, .Columns.Count).End(xlToLeft))
        If IsNumeric(Flag) And Flag <> 1 Then
            Dest = WorksheetFunction.Match(.Cells(DateRw, Flag.Column), _
                Sheets("Projections").Rows(DateRwP), 0)         'target column on Projections sheet
            .Cells(FR, Flag.Column).Resize(LR - FR + 1).Copy    'copy the range of data
            With Sheets("Projections").Cells(DateRwP - 2, Dest)
                .PasteSpecial xlPasteValues                     'paste values only to target
                .PasteSpecial xlPasteFormats                    'paste any cell format updates
            End With
            Application.CutCopyMode = False
        End If
    Next Flag
End With

Application.ScreenUpdating = True                           'back to normal
End Sub

I tried to comment as much as I could so it was clear WHAT each line of code accomplishes, that should aid as you try to decipher HOW it accomplihed that.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.