VBA for Supercharging Copy & Paste Between Workbooks

[Clint_Hill]

With details to follow, I ask: What is the level of complexity as well as the VBA programming experience required to code a program that:

Allows the user to select data in a source workbook and transfers the data to the destination workbook.

The proposed VBA program combines:

• Pasting data values
• Adjusts row quantity (with a predefined, constant starting row and adds rows before successive data)
• Changes the order of columns/ column data.

As a novice programmer, I appreciate your insights and links to specific VBA examples and VBA learning as well.

Previously, data transformation using Power Query proved to be a poor fit as the data source file in this project constantly changes.  

Thanks,

Clint

[#2 · Accepted solution]

@Clint_Hill You say the data file keeps changing. Would you be helped if the query would "just" pick up the most recent file in a folder?

[#3]

Thank you for your interest and suggestions.
The initial inquiry is lacking in detail that I will now provide to you.

SOURCE DATA CHANGE REASON
The source data changes as it is provided on a job-by-job basis from a customer in the form of an automatically generated, multi-worksheet workbook. The source data we need is stored in a consistently named worksheet.

CURRENT MANUAL PROCESS
Currently processing our purchase order worksheet (based on a workbook template) includes the user selecting the range in the customer workbook, manually pasting the range, deleting blank rows in the item list section.
Making it a real pain: The user now includes  formulas in the destination worksheet. As a part of this proposed P.O. improvement , these formulas will be transferred to a separate worksheet, removing a significant dependency.

MAIN INQUIRY / HOW TO
Ideas, tips, or code snippets of VBA to automate the following two steps are appreciated.
1.) In the source worksheet, the program is invoked by the user requesting a range.
2.) With the destination workbook open, the program populates (values only) data from the source range onto the destination item list.
3.) A predefined row defined in the program acts as its starting point.
4.) The source data populates the item list, rows are adjusted (with blank rows deleted).
5.) The program continues until a row containing the text string ‘Total’ is detected.
6.) The ‘Total’ row is preserved as is the successive PO data as their rows are advanced.
7.) The program ends.

Thank you,
Clint

[#4]

@Clint_Hill I've replied to your email