Avatar of Justin Smith
Justin SmithFlag for United Kingdom of Great Britain and Northern Ireland asked on

Help with variable range in copy and paste Excel macro needed!

Hi Guys, I am doing a simple copy and paste Macro in Excel. The Copied Cell is a fixed range, however, the pasted cell must loop across Row 5, find the Previous Days' date, then Offset by 1 row (having located the correct column) and paste the value. How do I do this?

VBA* MS Excel MacroVisual Basic Classic

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Justin Smith

Martin Liss

I'm not sure what you want to put where, but in the attached workbook the macro which can be executed by pressing Ctrl+Shift+F puts the C11 value in the found date column.
Justin Smith

Hi Martin, it works perfectly but i have just been told the Copied cell is in another spreadsheet:

Path is: \\15.SALES\0.Reports\DailyReports\SailySalesevenue\Reports\Sep 2021\20Sep\Desk_PL_Report_20-09-2021.xls". Range ("C11"). Is there a way you can tweak the code to reflect this?

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

This assumes that there are only two workbooks open and that the value to be copied is in the other workbook on a worksheet named "Sheet1".
Sub FindC11()
Dim lngCol As Long
Dim lngLastCol As Long
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb As Workbook
Dim ws2 As Worksheet

Set wb1 = ThisWorkbook
For Each wb In Workbooks
    If wb.Name <> wb1.Name Then
        Set wb2 = wb
        Set ws2 = wb.Worksheets("Sheet1")
        Exit For
    End If

With ActiveSheet
    lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    For lngCol = 1 To lngLastCol
        If CStr(.Cells(5, lngCol)) = Format(Range("G2"), "short date") Then
            .Cells(12, lngCol) = ws2.Range("C11")
            Exit Sub
        End If
End With
End Sub

Open in new window

Justin Smith


Hi Martin, due to Chinese Walls on my company computer, I can only send rudimentary examples. The Macro runs from a  spreadsheet called OMAN_PL_MACRO, goes into the attachment "Desk_PL_Report, copies a value in a fixed cell and imports it into OMAN_PL_MACRO, scans across Row 5 for the Previous Business date, eg, 21 Sep 2021, then pastes it in Row 6. eg. if the Date is in "(D5"), the Value is pasted in ("E5"). Could you adapt the attachments I have enclosed and return at all with working code?  
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.