Copy/Paste in value within 'With/Next'

[Matador_24]

Hello,

I am building a macro, I am in a section with a With / Next Loop.
I need to copy/paste in every loop the content of cell B3 because there is currently a vlookup formula where I bring data to excel. Thus, I need to copy/paste in value that cell.

Could you please help me with the code?

I tried macro recording:

Windows("LH Reporting tool v1.6 - Stonehenge.xlsm").Activate    Range("B3").Select   Selection.Copy    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    
Application.CutCopyMode = False

But that gives me an error in the code I think because I am in a loop,

Thanks in advance!

Luis

 

[JLGWhiz]

You don't need to copy and paste. Try it this way.

With Windows("LH Reporting tool v1.6 - Stonehenge.xlsm").Range("B3")
     .Value = .Value
End With

 

[Matador_24]

Hello,

Actually, how can I add it to my below code?

Worksheets("Balance Sheet").Activate
r = 3
With Worksheets("sheet1").PivotTables(1)
        'Cells(r, 1) = .PivotFields("Year").Name
        For x = 1 To .PivotFields("Top Level Object Reference").PivotItems.Count
            DealNumber = .PivotFields(3).PivotItems(x).Visible
            If DealNumber = -1 Then
                DealNameActive = "=VLOOKUP(B4,'V:\Real Estate\Portfolio Management\Daniela\Working files\[Chart of Account.xlsx]Partner Investments'!$B$1:$F$10000,5,FALSE)"

I prefer to avoid using the name of the file as it changes depending on the investment. How would you add it above? Please note that there is a With, and a For already,

Thanks!