Row find Macro

[James]

Hi

I'm trying to write some code so as that I can copy the last row in a sheet.
Each day a new row is added so I need it to find the new row each day. I have
written the below however although it keeps copying row 46 and not the last
one! Any thoughts??

Sheets("Sheet1").Select
Range("A1").Select
Selection.End(xlDown).Select
Range("B46:M46").Select
Selection.Copy

 

[Rick Rothstein]

Try it this way...

Sheets("Sheet1").Select
Cells(Rows.Count, "A").End(xlUp).Select
Selection.Copy
....etc...

 

[Mike H]

Hi,

LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow).Copy Destination:=Range("B46")

Mike

 

[Mike H]

Maybe I misread your post, try this

LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Copy Destination:=Range("B46")

Mike

 

[Rick Rothstein]

I just remembered, you wanted to copy columns B to M. Use this code instead
of what I posted...

Sheets("Sheet1").Select
Cells(Rows.Count, "A").End(xlUp).Select
Selection.Offset(0, 1).Resize(1, 12).Copy

or you could do it this way if you wanted to see the column letters...

Sheets("Sheet1").Select
Cells(Rows.Count, "A").End(xlUp).Select
Intersect(ActiveCell.EntireRow, Range("B:M")).Copy

 

[jason]

Hi

I'm trying to write some code so as that I can copy the last row in a sheet.
Each day a new row is added so I need it to find the new row each day. I have
written the below however although it keeps copying row 46 and not the last
one! Any thoughts??

Sheets("Sheet1").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Range("B46:M46").Select
    Selection.Copy

call me old fashioned but i would go about doing it this way:

do while workbooks(Yr_Book).worksheets(Yr_Sheet).cells(i,1)<>""
i=i+1
loop

last_row=i-1 'this may be i and not i-1, proof it when you test the
algo.

i=1

do while workbooks(Yr_Book).worksheets(Yr_Sheet).cells(last)row,i)<>""
i=i+1
loop

last_col=i 'again, may be i-1 i forget what happens.

workbooks(Yr_Book).worksheets(Yr_Sheet).range(cells(last_row,1),cells
(last_row,last_col)).select
selection.copy

'or
dim Holder(0 to 0, 1 to last_col)

for i=1 to last_col
holder(0,i)=workbooks(Yr_Book).worksheets(Yr_Sheet).cells(last_row,i)
end

'go to new workbook and output array.
'this is not the most computationally simple way, but the most
computationally precise.

 

[james]

Non-macro code. The macro code suffers if there are empty cells,
while the code below might find an empty row; you'd need to add a test
for empty rows and work back up until it finds a value:


Dim lngRows as long
Dim wksTemp as Workheet

set wksTemp = ThisWorkbook.Sheets("Sheet1")
lngRows = wksTemp.UsedRange.Rows.Count

wksTemp.Range(Cells(lngRows,2),Cells(lngRows,13)).Copy

 

[Rick Rothstein]

Are you the OP (hard to tell because the email addresses are different)?
I'll assume you are...

The macro code can be made to do what you want... you just have to tell what
that is. Your first post didn't give us much to go on and this second post
of yours doesn't really tell us too much more. Can you describe in words
what you have and what you want from it?

As an aside, if you are looking for the last row with any data (not formula
displaying empty string) in it, no matter what the column, then you can use
this...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

--
Rick (MVP - Excel)


Non-macro code. The macro code suffers if there are empty cells,
while the code below might find an empty row; you'd need to add a test
for empty rows and work back up until it finds a value:


Dim lngRows as long
Dim wksTemp as Workheet

set wksTemp = ThisWorkbook.Sheets("Sheet1")
lngRows = wksTemp.UsedRange.Rows.Count

wksTemp.Range(Cells(lngRows,2),Cells(lngRows,13)).Copy

 

[James]

Hi Rick and everyone else

Have been away all weekend (so no different James!) but thank you for your
responses and will take a look first thing this morning!

 

[James]

Hi Rick

Many thanks for this I tried it with the column letters and it seemed to
work. One more question of you don't mind I am now trying to paste the data
into another sheet and thought this was ok but seems not! I tried:

Sheets("Sheet2").Select
Cells(Rows.Count, "C").End(xlUp).Select
Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste

I used "C" as this is where the last line of data shows and F:Q as this is
where I want to paste it but doesn't seem to work?