Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target row

**david gonzalez** · 06-19-2014, 09:29 PM

Hi,

I'm learning VBA, Please need some help w/ vba to copy in same sheet but different rows if value condition is meet.
explanation:
there are 3 zones, almost identical in size and rows (already have formulas on it)

Zone 1 (BQ3:BW13)
Condition : IF Cell BU3 have a value Min/Max 4~12, Then copy Range content of Row BQ3:BW3
to First Empty Range target Rows AG3:AM3
If no condition es meet, then skip Paste action.

Zone 2 (BY3:CE13)
Condition : IF Cell CC3 have a value Min/Max 4~12, Then Copy Range content of Row
BY3:CE3 to next available target Row e.i AG3:AM3 (If not available, then Copy to next Empty Row Range AG4:AM4)
If no condition es meet, then skip Paste action

Zone 3 (BY3:CE13)
Condition : IF Cell CK3 have a value Min/Max 4~12, Then Copy content of Row
CG3:CM3 to next available target Row AG4:AM4 (If not available, then Copy to next Empty Row Range AG5:AM5)
If no condition es meet, then skip Paste action

Then the same actions as indicated above for Zone 1,2,3 but start on Row 4

Then the same actions as indicated above for Zone 1,2,3 but start on Row 5

Then the same actions as indicated above for Zone 1,2,3 but start on Row 6

the same Loop Till reach Last Row 13.

Please find excel sample attached

Thank you !!

Best Regards

David

**jolivanes** · 06-20-2014, 02:29 AM

Is this what you had in mind?
Click on the "Maybe" button on your sheet (see attached)

Please Login or Register  to view this content.

**david gonzalez** · 06-20-2014, 02:01 PM

Hi jolivanes,

Yes sir, i exactly what i need it to do..

i'm very new with VBA, Question : What about if i add an extra 3 zones 4,5,6 ??
it is going to get the same length in rows an Column as Zone 1,2,3,But starting 2 column at right,

I suppose that i have to modify

Please Login or Register  to view this content.

to whatever size ??
(i'll try to see what happen..lol )

Question what's the purpose of

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

what it means??

Sorry, i'm a lil dum with VBA.. Still learning :-)

Appreciated, Thank you so much !!!

Best Regards

David

**david gonzalez** · 06-20-2014, 02:55 PM

Hi jolivanes,

When adjusted the line "For i = 69 To 85 Step 8 " to whatever new X to zz value,
it work perfect with New zones 4,5,6

Thank you !!!

Best Regards

David

**jolivanes** · 06-20-2014, 03:08 PM

Hi David.
The only dumb thing you can do is not asking questions.
The 69 and 85 are your columns, numbers instead of alphabetical. The number 69 is the first column of the first zone.
To change your Column references to RC, as it is called, and back, check out the following site.
https://kb.wisc.edu/page.php?id=781#2007
You'll see that the column references change from A, B, C, D etc to 1, 2, 3, 4 etc.
The number 8 in Step 8 means that from 69 you go to 69 + 8 = 77 and the next step + 8 again = 77 + 8 = 85.
Each so called zone is 7 columns wide + 1 empty column = 8
So for every zone you add you'll have to change the 85, which is the first column in the last zone in your current example,
to the number of the first column of the last zone, providing everything is the same. (7 columns for the zone, empty column, 7 columns again, etc etc)
If my math is right, if you add 3 more zones you'll end up with

Please Login or Register  to view this content.

References to cells can be made as Range("B4") or as Cells(4, 2). You'll notice that one has the column first (B) and row second (4)
With Cells it is the other way around. Row first (4) and Column second (2).
So if you would put both in code, with .Select following it, you would both times select the same cell.

The message "Select Code, Copy To Clipboard" is if you want to copy the code so you can paste it in a module.
In this case you don't need that because the attachment has the code in it.
If you have any questions, don't hesitate to ask. There are wonderful people on these forums that gladly help people that want to learn.
All of these people offer their time for free to help.
Regards
John

**david gonzalez** · 06-20-2014, 07:37 PM

Hi jolivanes,

Is not word to apreciated and say Thanks to your explanation on previous mail..

for instance, I did not that references to cells can made a range ("B4") or as Cells (4, 2) (the other way around).... that's why sometimes codes and some ranges didn't make sense and confuse me instead.

I know some about formulas and might 1% of of code, lol.., but i realize that still there are some lil "holes"
on these thing that i know about excel.

Thank you so much !!!

Best Regards

David

**david gonzalez** · 08-07-2014, 05:38 PM

Hi Mr Jolivanes,

after ask for help and modified your code and with out success,
i would like to ask you :

1-. If one Zone is 7 steps + 1 offset = 8
and
2-.The number 69 is the first column of the first zone. (RC reference)
and
3-.The 3 zones are located from RC 69 to RC 85
and

Please Login or Register  to view this content.

4-. RC 33 is where the row get the information (from hidden rows/info RC69 - RC85)

Then (my dumb question)
How do i do to (or what do i have to modify)
IF
1-.Apply only 1 zone with 13 Step (Starting in RC64 to RC76)
2-. and start to get the info starting RC 28 (no RC33)

Best Regards

David

**jolivanes** · 08-08-2014, 12:05 AM

If you have only a single so called zone, that would be a total different code.
Could you attach a workbook with a before and after just like you did before.

**jolivanes** · 08-08-2014, 02:29 AM

I am not sure if I understand you right but try the following on a copy of your workbook.
(Always try code on a copy of your workbook because there is no "Undo" for code.)

Please Login or Register  to view this content.

It works on one block (zone) of cells from Column BL to BX from row 4 to row 13 checking column BP for the values.
It will paste the values in column AB (28)

If my assumptions are off, let us know.
Good luck

Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target row

LinkBack

Thread Tools

Rate This Thread

Display

Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target row

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Re: Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target r

Thread Information

Users Browsing this Thread

Similar Threads

VBA Code to Delete Rows in One Target Worksheet Based on Value Found in One Column

Need code to copy and paste based on variable rows

Macro to Copy Rows based on target cell value and paste in another sheet. Help Needed!

Copy and paste values from a range of cells to a target cell based on a countdown clock

Copy and paste based on target address

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions