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!
James
markx wrote:
> 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
>
>
> "Zone" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> > markx wrote:
> >> 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
> >>
> >>
> >> "Zone" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Your closing parenthesis in the wrong place.
> >> > FirstFreeRow = Range("B7").End(xlDown).Row+1
> >> > James
> >> > markx wrote:
> >> >> 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
> >> >
> >
|
Similar Threads
