Macro that Inputs Next Line

[chemmiah]

Hello All,

Attached is my Master List. Some data is vague because it is confidential. I need help with backlogging data and I have a giant list.

I want to use that list to run some data manipulation one by one, but i am hand doing it. I am running the same calculations so i figured that i could just have a macro that reads the next line and inputs the data already there.

On the master list, there are two tabs: Main and Data. Main is where the calcs take place and data is where i have a hug list of data. ( I have already spaced out the placement based where it is in my real list, that is why there are huge gaps.)

What i need is a macro that reads the first line of data and inputs the information on the main tab based on the column it is in. Then when run the macro again it moves down the list and input the new data.

It would even be better to include a code to reset the starting position to the first line in case of an accident.

Let me know if you need more info.

[Chris 53]

Need more information.

I can see no formulas in anything. So it looks like you have entered information almost at random and haven't told us how to get from one set of data to another.

Unfortunately my telepathy skills aren't up to the challenge.

[chemmiah]

The Data on the data tab has confidential information and is added by employees. Don't worry about the equations they are in other cells that aren't important.
What i want to do is have it so the spots on on the main tab that have letter correspond to the column in the data tab. For instance, in the Main Tab, Cell A2 says BP, this correspondence to Column BP in the Data Tab.
The first time i run the macro i want it to input the values from the data tab into the main tab according to the column positions in row 1, when i run again, it does the same thing but moves down to row 2 and so on
Right now, I copy and paste the data from the data tab into the main tab one line at a time. The flow is like this:
Cut and paste data; Run equation macros (they are fine); Cut and paste next line; Run Equation Macros, etc.
I want to eliminate the cut and paste portion

[chemmiah]

did this make sense?

[Chris 53]

You need to post a sample spreadsheet, with some sample data, I'm afraid I can't pick up what you want from the descriptions you are typing.

[Chris 53]

How about having a cell on Main to say which row on data you want to read, say A1, then use this in a formula to get the other fields.
For example the formula in A2 would be =OFFSET(Data!$A$1,$A$1-1,67). I'm sure you could work out the other formulas if this result is what you want.

Then you would only have to increment A1 to get the next row's data.

Have I understood some of what you are asking for?

Chris

[chemmiah]

What i normally do is highlight the data:
2/11/2009 323 X 196 Person Floor -0.572490586 1 A X Green or Yellow 20 # of Hours
AND
2/11/2009 324 X 196 Person Floor -0.572490586 1 A X Green or Yellow 20 # of Hours
from row 1 on the data tab and insert at the exact spots on the main tab (starting at R52 and R53)

Then, I input the data on data tab for row 1 in the cells in the main tab (A2, A4, B3, B5)

Next, I run some equations with macros i have set up.

Then i start the same process with row 2, row 3, and so on.

The problem is if i use a direct cell equation, i.e. "=Data!r52" and then delete the row so row 2 is now row 1, all my equations are messed up.

I figured there has to be a way that if i run a macro it starts at a predetermine row and everytime you run it inputs the numbers in the spots i want instead of copying and pasting

[Chris 53]

The problem is if i use a direct cell equation, i.e. "=Data!r52" and then delete the row so row 2 is now row 1, all my equations are messed up.

Have you tried anchoring the references? change to = Data!$r$52. This won't change if you delete row one on sheet Data.

[chemmiah]

It still does this Data!#REF!

Also, this means i have to delete every row after every use, which slows down the process. I would rather leave the data and move the reference.

[Chris 53]

I refer you back to thread #6

This would allow you to type a row number into A1 and the data would change.

[chemmiah]

This works great. I am moving it to A6.

Is there a simple macro that i can run that changes that number in a6 by 1 everytime i run it?

[Chris 53]

If you run a equation macro once you have copied the data across why not add a line in that macro to change the value in cell A6 as the first operation in that macro?

The line to insert would look something like:-

range("a6").value = range("a6").value +1

Chris