Referring to Cells

[PeterM]

I have two workbooks open in Excel 2003. From WorkbookA I need to get the
currrent row number, add 1 to it and copy the contents from WorkbookA to
WorkbookB. Presently I have the following code that works...

Dim CurrentRow as Integer
Windows("workbooka.xls").Activate
CurrentRow = ActiveCell.Row
Range("a" & CurrentRow - 1).Select
Selection.Copy
Windows("workbookb.xls").Activate
Range("D2").Select
ActiveSheet.Paste

When the .Activate is executed, workbooka is displayed, then workbookb is
displayed when the second .Activate is executed...during that time it's
flashing windows all over the place...

my question: is there a way from workbookb to get the active row and cell
contents
from workbooka? this would eliminate the .Activates and appease my users...

thanks in advance for your help!

 

[rsw1984]

Hi

What you can do is you can just simply in Workbook B press "="

Then select WorkbookA so you would get this =[Workbook1]Sheet1!$?$?.

The above question marks showing the Cell Values. once you have done this
you can simply, copy the code =([Workbook1]Sheet1!$?$?)+1

Hope this is what you meant.

Thanks

 

[PeterM]

thanks but I know how to do it via formulas in the worksheet themselves, I
need the VBA code to do it...can you help with that?

Hi

What you can do is you can just simply in Workbook B press "="

Then select WorkbookA so you would get this =[Workbook1]Sheet1!$?$?.

The above question marks showing the Cell Values. once you have done this
you can simply, copy the code =([Workbook1]Sheet1!$?$?)+1

Hope this is what you meant.

Thanks

I have two workbooks open in Excel 2003. From WorkbookA I need to get the
currrent row number, add 1 to it and copy the contents from WorkbookA to
WorkbookB. Presently I have the following code that works...

Dim CurrentRow as Integer
Windows("workbooka.xls").Activate
CurrentRow = ActiveCell.Row
Range("a" & CurrentRow - 1).Select
Selection.Copy
Windows("workbookb.xls").Activate
Range("D2").Select
ActiveSheet.Paste

When the .Activate is executed, workbooka is displayed, then workbookb is
displayed when the second .Activate is executed...during that time it's
flashing windows all over the place...

my question: is there a way from workbookb to get the active row and cell
contents
from workbooka? this would eliminate the .Activates and appease my users...

thanks in advance for your help!

 

[rsw1984]

Sorry I don't do Visual Basic. Why do you need the code for that. I have
answered your original query.

thanks but I know how to do it via formulas in the worksheet themselves, I
need the VBA code to do it...can you help with that?

Hi

What you can do is you can just simply in Workbook B press "="

Then select WorkbookA so you would get this =[Workbook1]Sheet1!$?$?.

The above question marks showing the Cell Values. once you have done this
you can simply, copy the code =([Workbook1]Sheet1!$?$?)+1

Hope this is what you meant.

Thanks

I have two workbooks open in Excel 2003. From WorkbookA I need to get the
currrent row number, add 1 to it and copy the contents from WorkbookA to
WorkbookB. Presently I have the following code that works...

Dim CurrentRow as Integer
Windows("workbooka.xls").Activate
CurrentRow = ActiveCell.Row
Range("a" & CurrentRow - 1).Select
Selection.Copy
Windows("workbookb.xls").Activate
Range("D2").Select
ActiveSheet.Paste

When the .Activate is executed, workbooka is displayed, then workbookb is
displayed when the second .Activate is executed...during that time it's
flashing windows all over the place...

my question: is there a way from workbookb to get the active row and cell
contents
from workbooka? this would eliminate the .Activates and appease my users...

thanks in advance for your help!

 

[Dave Peterson]

Instead of using the activecell on the worksheet, can you determine it from the
data--like the last used cell in column A???

If yes:

Dim LastRow as Long
dim DestCell as range
Dim WksA as worksheet
dim WksB as worksheet

set wksa = workbooks("workbooka.xls").worksheets("somesheetnamehere")
set wksb = workbooks("workbookb.xls").worksheets("someothersheetnamehere")

with wksa
LastRow = .cells(.rows.count,"A").end(xlup).row
end with

with wksB
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

wksa.rows(lastrow).copy _
destination:=destcell

==========
This actually copies the last used row in wksa to the next available row in
wksb.