Posts by KjBox

    Like this

    Try this

    Try changing

    Code
    newFullPath = WBPath & "\" & WBName
    ThisWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

    to

    Code
    newFullPath = WBPath & "\" & WBName & "\"
    ThisWorkbook.SaveAs fileName:=newFullPath & CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

    Try this alternative approach, file attached


    Code assigned to the button

    CurrentRegion stops at an empty row, so the "Record Only" would not be included. Try

    Not surprised you were confused by this question, Rory!


    Hanes was trying to adapt a bit of VBA I had written for him to suit a couple of changes in the VLOOKUP tables.


    The solution he was looking for was:

    Code
            For i = 2 To .Cells(.Rows.Count, 7).End(xlUp).Row
                .Cells(i, lCol) = _
                Application.VLookup(Application.VLookup(.Cells(i, 6), r2, 2, 0), r, _
                Application.Match(Application.VLookup(.Cells(i, 8), r3, 2, 0), r.Rows(1), 0), 0)
            Next

    where r, r2 & r3 are Range variables set to 3 different tables in 3 different workbooks. The results are placed in a 4th workbook.

    Like this?

    Try this

    It is very difficult when your sample files bare no resemblance to your actual file, and you are unable to adapt code that works on your sample file to suit your actual file.


    You really need to attach your actual file and explain exactly what it is you need, then those trying to help you will not waste time.


    All I could go on was the code you had, which copied Column AH of the Source sheet to Column A of the Destination sheet, I thought you wanted to change that to column D.


    Now you want to use Columns I and J in the source file, is that correct?

    Thanks for the files you sent.


    First you need to change the Input file a bit, replace "Dom Rep" with "Dominican Republic" and replace "Trinidad & Tobago" with "Trinidad and Tobago"


    Then change your code to this

    Try this

    The code loads the data from Columns AH & AI of the Source Sheet into array x. Code then loops through array x and builds array y if the second dimension of x is not blank. The code then places the contents of array y onto the Destination sheet.