LastRow problem

pirdop

Board Regular
Joined
Jul 27, 2010
Messages
72
Hi,
I try to copy the date from multiple summary sheets(in differentr wbs) in one new workbook called for the moment Summary_test.xls

The problem I encounter is that the lastRow=ActiveSheet.UsedRange.Rows.Count

doesn't return the actual last line but the lastRow used - 1, any ideas what's wrong ?

here is the code

Code:
Sub Summary_In_NewWorkbook()

Dim i As Double
Dim wbname As String

wbname = ActiveWorkbook.Name

    Workbooks(wbname).Activate
    Sheets("Summary").UsedRange.Copy
    Workbooks("Summary_test.xls").Activate
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ' the msgBox was added after I detected the problem!
    MsgBox ("last row used is: " & LastRow & "lastrow+1= " & LastRow + 1)
    Cells(LastRow + 1, 1).Select
    Sheets("Sheet1").Paste



End Sub
Thank to all!

P.S. Of course with the lastrow+1 the first copied rows start from row 2 in the new workbook, which was not initially the idea
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pirdop

Board Regular
Joined
Jul 27, 2010
Messages
72
apparantely with each copy/paste operation made the error is increasing dynamically after 3 operations I'm getting 4 lines overlapping !???

Still don't get it why :eek:
 

pirdop

Board Regular
Joined
Jul 27, 2010
Messages
72
for those interested:

Strangely enough the first empty row was bugging the whole thing
so simple if check solved it

if isempty(cells(1,1))= true then
cells(1,1).select
Sheets(1).Paste
else
Rows(lastrow+1).select
Sheets(1).Paste
endif

although it's solved now I still don't understand this strange behaviour!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,002
Messages
5,508,725
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top