none
Anyone know how to do this? RRS feed

  • Question

  • Hello, I have a problem, and it will most likley be able to be solved by visual basic.  I have 2 sheets in Excel:

    1. On the first sheet I enter some data into some cells, into a table like format
    2. I have a macro button that will make the data I have inputed appear on the second sheet, I know this can be done with a simple copy and paste command
    3. But however I would like to make sure that when details are present in one line of cells on the second sheet, if I enter another set of details on the first sheet and then press the button again, the first set of details will not be overwritten, but the new details will appear on the next line.
    I have seen this done in some spreadsheets, but I have never known how to do it.
    If anyone knows how to do this, I would be most grateful if you could share.

    Thanks in advance
    Sunday, March 25, 2007 1:12 PM

Answers

  • The way you are saying what you want to do makes it seem like you are using your first sheet as a form to add data to the second sheet?!

    If this is the case then better to use a windows form...

    This is the way that I understand what you want...

    you enter data in the first sheet on a line and then push the button. It then in turn takes that line of data and sends it to the second sheet.

    You are wanting it to send to a line that is not already populated with data.

    Is that correct?

    Sunday, March 25, 2007 4:13 PM

All replies

  • The way you are saying what you want to do makes it seem like you are using your first sheet as a form to add data to the second sheet?!

    If this is the case then better to use a windows form...

    This is the way that I understand what you want...

    you enter data in the first sheet on a line and then push the button. It then in turn takes that line of data and sends it to the second sheet.

    You are wanting it to send to a line that is not already populated with data.

    Is that correct?

    Sunday, March 25, 2007 4:13 PM
  • Yes I thought a form may have been a good idea, ill try and demonstrate with images..

    Sheet1:

    Heading Heading Heading Heading Heading Heading "Macro button"
    Data Data Data Data Data Data

    Right data is arranged like the above in the first sheet, the macro button copys all the data onto the next sheet, the data will only be in single lines of cells, like the above, so sheet 2 will be like this

    Heading Heading Heading Heading Heading Heading
    Copied Data Copied Data Copied Data Copied Data Copied Data

    However, if I press the macro button with different details entered, the data on the Sheet 2 will just be overwritten, now I dont want that, I want it to recognise that there are values in those particular cells, and concequenctly use the next line available.. like so:

    Heading Heading Heading Heading Heading Heading
    Data Data Data Data Data Data
    2nd Line of Data

    Hope this makes things clearer.

    Thanks



    Sunday, March 25, 2007 4:50 PM
  • Well, you probably already have the answer to this since it was posted a year ago... but...

    I think that you just need a simple Loop statement something like this.

    Dim osheet1 as Excel.Workbook.worksheet
    Dim osheet2 as Excel.Workbook.worksheet
    Dim col = 1
    Dim row = 1

    'as you are passing the data check to see if the cell has a value.

    Do until osheet.cells(col, row).Formula = ""
    Row = Row + 1
    Loop

    'write data to the row that is open

    osheet.cells(col, row).formula = "new data"
    col = col + 1
    osheet.cells(col, row).formual = "some more new data"

    hopefully this will help you or someone in the future.




    MT
    Wednesday, June 25, 2008 1:38 PM