Efficient Approach to Pull Data from one File to Another & apply Calculations?

[liquidmettle]

UPDATE: NOT SOLVED BUT LINKED IN A MORE FOCUSED THREAD (New thread here: https://www.excelforum.com/excel-pro...ml#post5883877)

Hi All,

Its been a while since I've written a macro from scratch and I'm very rusty. Even referring to what I've previously done, I'm not sure I'm approaching my current task efficiently. How would you approach the following scenario?

TLDR: Copy specific columns from excel file in one folder and paste them into a report file that will be saved as a CSV. Because it will be saved as a CSV, I want to apply certain logic and formulas to each cell in the copied range (the application differing depending on which range).

Example 1: In the Data file is a column with the first and last name in a single cell. I'd want to apply the following formula TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",255)),255)) to paste only their last names in the Report file. I don't need to just paste and fill-down the formula because ultimately the report file will be saved as a CSV for upload into a different system, and I also wouldn't want to deal with a temporary helper column if I could avoid it.

Example 2: Some columns in the Data file have written values that I want to convert to numbers based on condition. I'll simplify that if A2 = True, then that cell should = 1 in the new sheet. Or 2 if false, 3 if unknown, etc.

With both examples cycling through each value in a column and applying the logic or a formula (and keeping only the formula's outcome) is what I can't remember how best to achieve.

I am planning to reference the files via the code below (that I've used successfully in previous applications) with the file structure below so that the staff using this can just move the parent folder around without worrying about breaking the filepaths:

File Structure:

Parent Folder

• Report Folder
• Raw Data Folder

Please Login or Register  to view this content.

I've previously then used the following to set up the mass copying of data from multiple files into a single file (but the ranges were contiguous and not single columns like I want to achieve now). Also I'll only be having it pull from a single file this time instead of a hundred.

Please Login or Register  to view this content.

The source Data file has no merged cells so simple parts like that can be trimmed, but how should I approach copying Columns A, Y, AG, BB to B, AH, W, G respectively and apply logic or a formula to change the output for each cell that requires those changes? (My final will have many more columns copied and not all will need to be modified).

The column headers differ in structure as well between the Data file and the Report file (so I'd want to keep the headers of the latter in row 1).

Additionally I would want it to save the file as a CSV in the report folder without requiring the user to do it. Basically the Report File is just intended to be a macro enable document with the structure I want that the macro will run from, pulling the appropriate columns over, and then saving that as a CSV.

Thank you in advance for any assistance you can provide both in the macro code structure and the logic of the process I'm trying to automate.

[liquidmettle]

I'll add that I do know how to start the copy range from row 2 to lastrow dynamically, but I'm at a mental road block of pasting it with the formula or logic conversion for the columns that need it. Or if the Row 2 to LastRow is even the best approach vs cycling through the values of each column (which I'm rusty on efficient loop generation).