Macro Query

[Errinmc]

Hi
I have a macro question that is driving me mad. Im trying to do a
macro that opens a spreadsheet and copies information from the 1st
spreadsheet into it. That part I can do fine, however this is updated
each day so i need the macro to put the new information at the bottom
of the spreadsheet - (this will change by one row each day) Can anyone
tell me how to do this. I have tried control end and control down arrow
but this seems to lock in the row and so each time the macro is run it
overtypes the last line.

Any help would be appreciated

Erin

 

[Dave Peterson]

You may be able to merge some of this into your existing code:


dim Rngtocopy as range
dim wks as worksheet
dim DestCell as range

with activesheet
set rngtocopy = .range("a1:B99") 'whatever
end with

'open the workbook
set wks = workbooks.open(Filename:="C:\book1.xls").worksheets("sheet99")

'find the next cell in column A that's available
with wks
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

'copy and paste
rngtocopy.copy _
destination:=destcell

'close that workbook that you opened
wks.parent.close savechanges:=false

 

[Dave Peterson]

Oops. You want paste into the newly opened workbook...

So change that last line:
wks.parent.close savechanges:=false
to something that makes sense:
wks.parent.close savechanges:=true

 

[iangore]

To find the rownumber of the last row do

ActiveSheet.UsedRange.Rows.Count

You could then do something like

newrow = ActiveSheet.UsedRange.Rows.Count +1
Range("a"& newrow).value = "New value added at bottom of sheet"

Ian G