Copying rows when inserting a column

Closed
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 - Nov 6, 2015 at 10:55 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 13, 2015 at 02:08 PM
Hello, everyone I have a problem with my database I am inserting new columns when there is a new company added and the go between two existing columns (the database is alphabetically done by companies(columns). So my problem is that there has to be an easier way than selecting and copying the heading and formulas each time I insert a new column between the two existing columns. Does anyone have any solutions on this.

Thank you for your help it is really appreciated

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 11:13 AM
seems to me that you are saying that new company needs to be entered so that alphabetical order is maintained. One easy way would be have the company information entered in a new sheets, then have a macro that would find the right spot to copy the stuff from the new sheet and paste in the appropriate location
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
Nov 6, 2015 at 11:27 AM
Thank you that sounds like what I need. But I have a few questions do I just have the new worksheet set up for the first two columns and all the rows that data is to inputted and can you just continually over-rite the existing information once the information is inputted into the correct position in the data worksheet alphabetically. That will also take care of any formulas I have or do the formulas need to be written in the worksheet that the new data will be entered.

Also can you provide the steps to do the macro.

Thank you for all your help.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768 > dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015
Nov 6, 2015 at 11:46 AM
Not knowing all, from the surface the design that i envisioned was new sheet is a temp area to allow for input. Once all information is entered, then a macro would copy the inputted information and insert in the location that it needs to be at. Once that is done, the macro clears out the temp sheet to allow for future input
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 6, 2015 at 12:26 PM
Thanks rizvisa1 that sounds like what I need. How would I go about doing that I am not very good at macros so treat my like a child and walk me through the steps please.

Thank you for your help
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 12:50 PM
1. Create a new Sheet
2. Enter the data for new company
3. start macro recorder
4. Manually copy the data to a column where you think it should go
5. Go to to temp sheet and clear all the data that you want to be cleaned
6. stop the macro recorder.

At this point what you have a general macro that would do most what you want but for selection of the appropriate column. Paste your recorded macro back here too see what can be done
0
dkayeh1 Posts 24 Registration date Monday November 2, 2015 Status Member Last seen December 4, 2015 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 13, 2015 at 10:16 AM
Thank you rizvisa1 you are very helpful just one question for this macro.

Is there a way to have a sheet where you are putting data in (say at the end of the columns-starting with column ZZ row 2 all the way down to row 451) and the macro copies the whole sheet and pastes it to a designated sheet over-writes the other data except row 1 and sorts the columns B through ZZ in alphabetical order. Can you help me with creating a code that does that or can it be done since row 1 across the worksheet is a database heading?

Any thoughts to creating this is helpful thank you in advance for your help.
0