adding data to an already existing macro

Question

Hello,

I have a report that takes data from one spreadsheet and places into another tab. Throughout the workbook there are different macros for each tab. I created a macro to combine them all. However, I realize in doing so, it won't work the next time I run it. On 2 of the tabs, the number of rows with data in it will increase (or actually decrease at the beginning of the month), so the way the macro is setup now, it's only going to look at the rows currently setup. The next time I run it, anything after that will be lost.

Is there a way to correc this, so that it will always adjust whenever new data is added?

Thank you!

Answer 1

Hi,

Yes there are several ways of doing that by finding the last used row in a column or worksheet and using that range. For example this finds the last row in column A

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

or if you want the last used row on a worksheet

LastRow = Cells.Find(What:="*", After:=[A1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

How you then set that up as a range depends upon what you're doing in you code but as a simple loop you can have

For x=1 to lastrow

next

Answer 2

Choose your insert point with something like this,

Cells(Rows.Count, 1).End(xlUp).Offset(1,0)

That is the equivalent of starting in A1048576 and tapping Ctrl+<up arrow> then moving down one cell. More help may be forthcoming if you posted some of the actual code.