# Posts by larbec

• ## Macro to clear out all cells

Re: Help writing a Macro to clear out all cells

If you need an external book too I can provide one but I would think as long as you had the input name or just leave that part blank for me to fill in is fine. One book is called (SSD (Z:) EXCEL SHEETS........... They are all linked to a book in this drive

• ## Macro to clear out all cells

I would like a Macro to clear out all the data on my sheet and replace it with new data from the next row(s)up. My sheet is linked to 4 external books and will be getting data from 18 sheets per book. How can this be done? This data I need to link from external books are cells CDEF NOPQ XYZ AA 3:35. I do not have any issues linking or anything else only with clearing out the data and placing the new data 1 row up from the external sheets and the other data comes from the "Drawn Numbers" tab within this same book. I know I can do it by copy and paste and also write a hard formula but would like a Macro (I am not familiar enough to do myself) so I can just copy and paste into my other books.

Thanks Gurus!!!

http://forum.chandoo.org/threa…ste-new-row-s-data.17612/

## Files

• ND Sheet.xlsx

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

This one is solved thanks to Luke M, he did an AMAZING job!!!!! THANK YOU THANK YOU THANK YOU Luke!!!!!!!!

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

I am trying to duplicate what is happening in columns HIJK 30-44 in columns OPQR and VWXY. E and U are in columns HI OP and O and D are in columns JK QR. I will have 2 rows of numbers as in rows 43 and 44 OPQR and VWXY.

If 1st movement is a U or E column(s) H or I OR O or P will advance UP 1 digit from 1111 to 2111 or 1211 in OPQR or 3222 or 2322 in VWXY.

If a 1st movement is a O or a D column(s) Q or R OR X or Y will advance up 1 digit from 1111 to 1121 or 1112 in OPQR or 2222 to 2232 or 2223 in VWXY.

2nd movement UP row 41 will have to be in column(s)P if U in OPQR or V in VWXY. You can not advance up a row if you have a single cell like the 3 in O42 or the 3 in W42

3rd movement Up is an O or D so either Q or R in OPQR will go up or X or Y in VWXY. Because there is an even number of cells like 4 1's vertically in columns Q and R rows 41-44 and columns X and Y in rows 41-44 either column can advance Up. I will pick Q and Y to go Up

4th movement Up is another O or D so again it will move in Q or R OR X or Y. Because there is a single cell in Q40 and Y40 columns R and X have to go up even though there is an Odd amount of cells vertically 40-44 (OPQR VWXY) The only place it can advance Up with an od number of cells is from the starting point on the bottom rows. After it gets a foundation of even number of cells vertically it will stay even.

The next movement Up is an E or U so because there is a even number of cells vertically in columns O 39-42 and W 39-42 they are the ones that advance Up

The next one to advance is another E or U so the P or V will go Up

This is how the counters move up. The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade

I hope this makes better sense and again. THANK YOU for your assistance

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

I have asked an additional forum for some help. I want to make sure I am following the board rules

http://www.excelguru.ca/forums…nd-rows&p=13424#post13424

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

Here is a video with sound. Sorry for any inconvenience

https://app.box.com/s/6nrn38ndxsaefz6s9vsc

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

I just realized my spread sheet was inaccurate as far as the conditional formatting so my apologies. This one is correct. This video has sound and hopefully someone will be able to help. I REALLY appreciate all the assistance thus far. I just realized my spread sheet was inaccurate as far as the conditional formatting so my apologies. This one is correct. This video has sound and hopefully someone will be able to help. I REALLY appreciate all the assistance thus far. I can have 1 button per row or 1 button to do them advance all at once

Video is on its way. Here is a corrected file with proper CF

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

I am getting a new microphone so sorry about no sound. Here is another sheet that may help

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

I made a video in hopes someone can figure out how to do this. Thanks for looking at it

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

Here is another example that may be easier to understand

I also have this on another forum and getting some help but not quite there yet

http://forum.chandoo.org/threa…-based-on-criteria.17172/

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

No flame here Roy

I deleted them due to the sensitivity Some of my books are 2 Gigs each and hard to make examples so my apologies if it has caused any issues. That's definitely not my intentions. Hopefully what I posted will be enough information to give me my end results. I am doing it one cell or row at a time and have to to 90 of these (each 4 columns) per book. Each book has 60 sheets x 4 to 5 books which is extremely time consuming. So, if this can even be done it will surly speed up my time but more importantly no mistakes. Seems being human I make mistakes lol.

I know someone can figure this out. Honestly, this board has some of the BEST programmers I've ever run across. Kjbox is by far the bomb that guy is AMAZING and I REALLY appreciate all his hard work, effort and knowledge. I'm learning VBA but have a long way to go so I also appreciate everyones patience and THANK YOU Oz for a FANTASTIC site and tutorial

Blessing!

• ## Automate copying rows based on counter number increment

Re: Automate copying rows based on counter number increment

Here is the file. I had to shorten it from 3000 rows down to 100 so Macro May not run properly without changing range. The written Macro allows me to click on the cells in row 1to advance the counters.

Thank you all for your suggestions

• ## Automate copying rows based on counter number increment

Re: Can this be automated

Quote from royUK;715713

I did not delete my link AAE did. I am trying to do a smaller worksheet

• ## Automate copying rows based on counter number increment

I will try my best to explain this. I have 4 digit counters that always count up 1-2-3-4-5-6-7-8-9. When they reach 9 they start over again with 1 so 1-2-3-4-5-6-7-8-9-1

The way they work is the 1st and 3rd digit is either Even or UP, the 2nd and 4th digit is either Odd or down. (even though they always go up LOL) Even and UP is Green in color and Odd or Down is red. Right now if I want to copy what the counter does in say columns HILK OPQR VWXY I either have to copy and paste each row or click on cells 1H 1I 1J 1 K etc.... I was wondering..... if I place a new counter number in two rows .... can it do it automatically run without me having to do one row at a time. I would like to place the 2 counter numbers in AC AD AE AF 3010 and 3011, AJ AK AL AM , AQ AR AS AT. Place the U/D or E/O vertically in column(s) AG AN AU and it will do the counters automatically for me by clicking on a button that says "go". Does this make sense?

Is this possible?
I want it to go from rows 3010 to 3060

Is this possible?

Thanks for looking this

I am trying to shorten worksheet so I can upload it

• ## Other sheets will not work in book

Re: Other sheets will not work in book

THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!

:dance:

• ## Other sheets will not work in book

Re: Other sheets will not work in book

[ATTACH=CONFIG]60820[/ATTACH]

How can I get the digits to go to the far right when I press HIJK? I tried formatting the sheet and they still print out in the center and sometimes to the left. I have to fix every cell. Thanks!!

• ## Other sheets will not work in book

Re: Other sheets will not work in book

Thanks Kj, duh on the B3 through NA3100, I knew better LOL

• ## Other sheets will not work in book

Re: Other sheets will not work in book

I understand the red is as red as I will get lol, the O and D need to be "red" right now they are green. The E's and Us need to be "green".

Also, how do I change the size of the font and style. Presently its larger than the one I have and I have to reformat every time I click on a cell. I am using 11 Calibri for my numbers (hate to be a PITA) and I do not mind changing it

Thanks Kj

• ## Other sheets will not work in book

Re: Other sheets will not work in book

Everything is working perfect except the sheet is giving me the date after the date beginning with column (AB AI AP AW BD BK BR BY CF ....MN) these columns should be empty

[ATTACH=CONFIG]60778[/ATTACH]

This is very minor and I have been messing with the Vba code but cannot seem to find the right color. The E's and U's should be bold dark green, the O's and D's should be dark bold red

• ## Other sheets will not work in book

Re: Other sheets will not work in book

I think I have this one figured out