need macro to copy to 2nd worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pub - 24 May 2008 07:42 GMT

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

Reply to this Message

Dave Peterson - 24 May 2008 12:11 GMT

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

> i hope this is possible, i need a little help.
> i use 2 excel files.
[quoted text clipped - 37 lines]
>
> thanks

Signature

Dave Peterson

Reply to this Message

pub - 24 May 2008 18:07 GMT

> You could make sure that the worksheet that needs to get copied is the
> activesheet.
[quoted text clipped - 25 lines]
>
> End Sub

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!

Reply to this Message

Dave Peterson - 24 May 2008 22:39 GMT

You scared me!

Glad you got what you wanted.

<<snipped>>

> 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!

Signature

Dave Peterson

Reply to this Message