Excel Forum

Copy Row Range to Next Row if Two Criteria met

Sherry Fox

Poinciana, FL

Member

Members

Forum Posts: 150

Member Since:
December 4, 2021

Offline

June 2, 2022 - 1:52 am

I am back (again), and thanks in advance! I am creating a workbook (sample attached). I need to have the row copied/pasted (as values) into the row below if TWO criteria are met. I have completed data entry for this first record. And since there is a YES in column L and a number greater than 0, that should trigger a change even to copy the data in columns B-O to the next row. Obviously this process must function anywhere on the sheet based on row 3 as the beginning of the data. And just in case, let me know where to change the data beginning at row 2, as the stakeholder may prefer that, despite they said they wanted this! LOL

Column L (Multiple Requests) - Yes

Column Q (#) - greater than 0

Note: The Blue cells in Columns A & Q contain formulas

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 3, 2022 - 8:53 pm

Hi Sherry,

The layout is not the most inspired one, it will cause problems in finding the last row because of the formulas and colors.

Why not using defined tables, can you use them?

Sherry Fox

Poinciana, FL

Member

Members

Forum Posts: 150

Member Since:
December 4, 2021

Offline

June 7, 2022 - 5:37 am

@Catalin Bombea,

By "defined Tables, do you mean this? And then how would I achieve my goal?

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 7, 2022 - 1:54 pm

Here is a resource that explains what are defined tables:
https://www.myonlinetraininghu.....cel-tables

They are special objects that can be easily manipulated from visual basic, the code will refer to the table object directly, not to the sheet ranges, so it can be placed anywhere in sheet, gives you more flexibility.

Sherry Fox

Poinciana, FL

Member

Members

Forum Posts: 150

Member Since:
December 4, 2021

Offline

June 10, 2022 - 12:08 am

@Catalin Bombea,

I was confused by the term "defined Tables". I use tables on a regular basis (especially since I love Power Query), just never referred to them as "defined tables". The format was given to me by the stakeholders, and is simply a working draft. Before I put any effefort into a redesign, I wanted to know if their request was possible.

Catalin Bombea

Iasi, Romania

Member