need macro to copy to 2nd worksheet

[pub]

i hope this is possible, i need a little help.
i use 2 excel files.

the 1st file (i dont think this matters, but) the filename always changes
InvoiceNumberTenantName.xls
row 45 summarizes everything on the invoice to 1 row A45 to R45
i just copy the whole row then paste it to the 2nd file

the 2nd files name does not change SummaryWithStatement.xls
Sheet1 is where i paste (paste values) my row from the invoice file.

this is the difficult part...

this Sheet1 is a dynamic range so i would have to find the 1st empty row
to paste.


when i recorded the macro i was at A1, and scrolled down (i see why i
would dont think i need that part). i had the 2nd file open, in fact...i
will always have the 2nd file open when i do this.
but just in case it matters, they are all saved in a folder C:\work
\invoices\open

so far this is what i have

Sub CopyRow()
'
' CopyRow Macro
'

'
ActiveWindow.SmallScroll Down:=30
Rows("45:45").Select
Selection.Copy
Windows("Summarywithstatement.xls").Activate
Range("A197").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

thanks

 

[Dave Peterson]

You could make sure that the worksheet that needs to get copied is the
activesheet.

Then do something like:

Option Explicit
Sub CopyRow2()

Dim LastRow as long
Dim RngToCopy as Range
Dim DestCell as range

with activesheet
'I used column A to get the last row
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtocopy = .rows(lastrow)
end with

with workbooks("SummaryWithStatement.xls").worksheets("SheetnameHere")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
End with

rngtocopy.copy
destcell.pastespecial Paste:=xlPasteValues

application.cutcopymode = false

End Sub

 

[pub]

You could make sure that the worksheet that needs to get copied is the
activesheet.

Then do something like:

Option Explicit
Sub CopyRow2()

Dim LastRow as long
Dim RngToCopy as Range
Dim DestCell as range

with activesheet
'I used column A to get the last row
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtocopy = .rows(lastrow)
end with

with
workbooks("SummaryWithStatement.xls").worksheets("SheetnameHere")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
End with

rngtocopy.copy
destcell.pastespecial Paste:=xlPasteValues

application.cutcopymode = false

End Sub

pub wrote:

Dave,
that was perfect, wait no it wasnt. it now looks up the last used row on
my original in case i want to add or delete rows...so its better than
perfect.

Thanks A Lot!

 

[Dave Peterson]

You scared me!

Glad you got what you wanted.

pub wrote: