Microsoft Excel macro question?

Question

I have created an Invoice system on Microsoft Excel.

When I run the macro, the screen flickers and the information goes into the first row.
However, when I want to run the macro again with different information, the new information goes into the same row as the first one and overwrites it.

Can anyone please tell me how to get the new information to go into the next row down and how can I get the screen to stop flickering?

Thanks

Answer 1

To prevent almost all 'screen flicker' in Excel VBA, add this line at the beginning of any macro that results in 'flicker' when called:



Application.ScreenUpdating = False



You do not have to turn it back on at the end of the macro, although most code I see does turn it back to 'True' before the macro ends. However, Excel automatically resets it to 'True' when the macro ends.



To always enter additional data in the next available row, you can use a macro that incorporates this method:



Sub CopyToNext()

ActiveSheet.Range("A1").Copy Destination:=Sheets("Sheet2"). _

Range("A" & Rows.Count).End(xlUp).Offset(1)

End Sub



The 'Rows Count... End(xlup) finds the last used row in column A, working from the bottom up. The 'Offset(1) portion tells Excel to put the data one row after the last used row.



The full syntax is Offset(rows, columns). If you just enter one number, that is perceived to refer to rows. Positive numbers move down for rows, right for columns. Negative numbers move up for rows, and left for columns.



Now, if there is nothing in cell A1 on the 'copy to' sheet, the first iteration will copy the data to cell A2, then A3, etc. If you don't want a blank cell in A1, enter a heading in it.



Edit: there are some situations that if you invoke Updating = False, it will prevent segments of your macro from executing. If you set it to prevent flicker and your macro does not perform normally, you may have to just live with the flicker.

Answer 2

You could try this. Turn on the macro recorder and enter some data to be copied, navigate to the top of the listing that you are creating in the leftmost column and hit Ctrl + down arrow. This will take the cursor to the bottom of the list. Then paste. Return to the home cell where you are entering data and stop the recorder.



Examine the code to see how this is coded and incorporate this code in your macro.

Answer 3

Sorry but you haven't really given any information that's relevant to the problem or the solution. "i could have the information slightly off the page of the spreadsheet" doesn't actually mean much to anyone but you. To help you we would need much more specific information, including representative data and layout. If the data you want contains specific keywords , either fixed or entered via some form of search / look up dialog , then you should be able to iterate the cells in the worksheet, check the contents for the keyword, and then extract the relevant information for display. If the information is based on the contents of other cells then that too could be extracted/searched but again we would need more specific information before we could really suggest a solution.