Copy/paste macro blocking

  • Thread starter Thread starter markx
  • Start date Start date
M

markx

Dear All,

I prepared a code that should:
- copy last used line from currently used workbook
- paste it to the first free line on all other opened workbooks

For some reason, the code blocks on the line:
FirstFreeRow = Range("B7").End(xlDown + 1).Row


Can you tell me what should I modify?
Thanks a lot,
Mark
-----------------
Here's the code:

Sub Macro7()

Dim wbk As Workbook

lastDataRow = Range("B7").End(xlDown).Row
Range("B" & lastDataRow).Select
Range(ActiveCell, ActiveCell.Offset(0, 9)).Select
Selection.Copy

'it would be also useful to exclude the file we just used, but at the end I
can also live without it (=delete the double row manually after the macro is
executed)
For Each wbk In Application.Workbooks
wbk.Activate
Worksheets("Sheet1").Activate
FirstFreeRow = Range("B7").End(xlDown + 1).Row
Range("B" & FirstFreeRow).Select
Selection.Paste
Next wbk

End Sub
 
Hi, thanks...

It's strange, because it only puts the problem to the next line of code...

Code stops at:
Range("B" & FirstFreeRow).Select

I'll try to work on this, but if you have a clue what's going wrong, pls let
know...
Cheers,

Mark
 
markx, Did you dim FirstFreeRow as Long? When the code chokes, go into
debug and rest the mouse over FirstFreeRow on the highlighted line. It
should show the value of FirstFreeRow. What is it? James
 
OK, I understand it better now - didn't know that you can actually see the
value of the variable...
First Free Row was 65537 (on one of the worksheets, there was no record
under B7, so it went to the very last row on the worksheet).
So if I want to use this code, I should find some solution for this kind of
situations... Were you already confronted with this?
* * *
By the way, I used another code, going from the bottom of the worksheet...
it works!
Only needed to change Selection.Paste to ActiveSheet.Paste (don't know
exactly why...)
---------------
Sub TheCodeThatWorks()

Dim wbk As Workbook

lastDataRow = Range("B7").End(xlDown).Row
Range("B" & lastDataRow).Select
Range(ActiveCell, ActiveCell.Offset(0, 9)).Select
Selection.Copy

For Each wbk In Application.Workbooks
wbk.Activate
Worksheets("Sheet1").Activate
Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
Range("B" & Lr).Select
ActiveSheet.Paste
Next wbk

End Sub
 
I almost invariably go from the bottom up, so I don't run into this.
If you want to use xlDown, you could probably avoid the problem with
something like
If LastFreeRow>65536 then LastFreeRow='something else!
Cheers!
 
Back
Top