Merging data - starting row x instead of first row

[Shiro26]

hi
I have X sheets which contain data in a fixed range R5C1:R13C46.
I am trying to create a master tab with the data of each tab.
I plan to import the title line at the end only

I wrote the code below but it always start pasting in the Master tab from row 44.
I am not able to figure out why...

Can someone tell me where the error is because it is kind of driving me crazy ....
Thanks

Shiro

Sub merge_v2()

Dim sht As Worksheet ', csheet As Worksheet
Dim i As Integer
Dim nbofsheet As Integer
Dim firstcol As Integer
Dim lastcol As Integer
Dim firstrow As Integer
Dim lastrow As Integer
Dim nbofrows As Integer
Dim masterarr() As Variant 'Master array to copy

Application.ScreenUpdating = false
firstcol = 1
lastcol = 13
firstrow = 5
lastrow = 46 'total row is 46 but we skip the 4 first rows
nbofrows = lastrow - firstrow + 1


Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Master").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Master"
    Sheets("Master").Cells(1, 15).Value = Format(Now(), "dd-mmmm-yy h:mm:ss")
    Cells(1, 1).Activate
    
    
nbofsheet = ThisWorkbook.Worksheets.Count - 1
    
i = 0
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then
            If sht.Name <> "Master" Then
                sht.Activate
                sht.Range(Cells(firstrow, firstcol), Cells(lastrow, lastcol)).Copy
                Sheets("Master").Cells((i * nbofrows) + 1, 1).PasteSpecial 'xlPasteValues

                Application.CutCopyMode = False
'
                i = i + 1
            End If
            sht.Activate
        End If
      Cells(1, 1).Activate
    Next sht
    Sheets("Master").cells(1,1).Activate
Application.ScreenUpdating = True

End Sub

 

[Kaper]

Well, I think we went quite far off from thread title. For those who use search engines and/or https://www.mrexcel.com/forum/search.php it is probably big disadvantage. So I'd like to suggest going back to main question, and start separate thread about compatibility etc.

 

[Shiro26]

Not in the title, under the file menu you might have a compatibility section with a convert button.

Hi Mark,

Apologies for my late reply.
Thank you for you help.
Indeed this was the issue. When I closed the book and re-opened it, it turned out that I could run the macro and go beyond the 65k lines.
It was the compatibility that was the issue.
I have never noticed this so thank you for pointing this out.

Kind Regards

Shiro

 

[Shiro26]

Well, I think we went quite far off from thread title. For those who use search engines and/or https://www.mrexcel.com/forum/search.php it is probably big disadvantage. So I'd like to suggest going back to main question, and start separate thread about compatibility etc.

Dear Kaper,

Allow me to disagree with you.
The original question was to find out why my macro was not working.
Thanks to the pertinent remarks from you guys, now i understand what was the issue.

Now it is clear and I do not need to put another thread on compatibility issue.

If I need further details on the compatibility functionalities, I will make some research but at this point it is not necessary.

Thank you,
Great week ahead to you

Best Regards

Shiro

 

[Kaper]

Dear Shiro,

Sure, you can have totally different opinion.
But there is a chance that you don't know rationale behind my answer, so let me just add few words of explanation:

The thread title is:
Merging data - starting row x instead of first row

Behind the main focus there was your general problem "why my macro was not working". So once you have open thread you try to deal in it with other issues, and assume it's the best way to go.

But answering on fora such like this one, people tend to see that they do not help not just one particular person, but rather spred their knowledge and share skills (wow, It's high C, isn't it ) to the whole community (and the person who asked was just a representative of this community, who foirst encountered such problem, or was not successful in searching).

So thinking in categories of wider community of excel help seekers - one thread shall be rather devoted to one problem.

Searching for good sailing Carribean holiday you will probably never browse SkiingInTirol tourist agency offers (judging them just by name/title).

Additional points which could convince you:

With descriptive thread title you may receive better answer. Because you gather focus of person who is/feels competent in specific field.

And you may also consider fact that fresh threads, and especially Zero Reply Posts ( https://www.mrexcel.com/forum/search.php?do=process&type=[]=1&replyless=1&replylimit=0&forumchoice[]=3&childforums=1&forumchoice[]=4&childforums=1&forumchoice[]=23&forumchoice[]=11&forumchoice[]=13 ) gather more attention.

So that is my point of view and that's why I suggested to ask separate question about compatibility (But using search before posting - may be it's already answered).