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]

You first calculate

firstrow = 5
lastrow = 46 'total row is 46 but we skip the 4 first rows
nbofrows = lastrow - firstrow + 1

so it is 46-5+1 = 42 and then use:

Sheets("Master").Cells((i * nbofrows) + 1, 1).PasteSpecial 'xlPasteValues

for first case (i=0) probably there is nothing to copy in

[COLOR=#574123]                sht.Range(Cells(firstrow, firstcol), Cells(lastrow, lastcol))[/COLOR]

so next step (i=1) you paste to row 44 (43? but again 1st row copied was empty?)

Cells((i * nbofrows) + 1, 1)

 

[Shiro26]

Hi Kaper,
In fact, it happens because of the sheet Acerno_Cache_xxx
I had to exclude both the mastersheet and the Acerno_Cache and therefore ended up with the code I put below my signature.

NEVERTHELESS, I have a remaining issue....
Although I can perform such code on a 900 sheets excel file, it turns out that when I run it on my 2000 sheets excel file, the moment the macro reaches copy of sheet1561, the process breaks on the line

                        Sheets("Master").Cells((i * nbofrows) + 2, 1).PasteSpecial xlPasteValues

Has somebody an idea why? 1560*42=65520 it is kinda close to 65536...

Thank you very much
Shiro

'without Array
'Sub Step1_Name_and_merge_v2()

Dim sht As Worksheet ', csheet As Worksheet
Dim i As Integer, start_time As Date, end_time As Date
Dim nbofsheet As Long
Dim firstcol As Long, lastcol As Long
Dim firstrow As Long, lastrow As Long
Dim nbofrows As Long

start_time = Format(Now(), "dd-mmmm-yy h:mm:ss")

Application.ScreenUpdating = False

Dim accountnb As String
Dim accountname As String


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 = "Start_time"
    Sheets("Master").Cells(2, 15).Value = start_time
    Cells(1, 1).Activate
    
nbofsheet = ThisWorkbook.Worksheets.Count - 2 '-2 because there is the Master sheet we created above as well as the Acerno Cache XXXX
    
i = 0
    For Each sht In ActiveWorkbook.Worksheets
        Debug.Print "Started at " & start_time & sht.Name & " out of " & nbofsheet
        If sht.Visible Then
            If sht.Name <> "Acerno_Cache_XXXXX" Then
                If sht.Name <> "Master" Then
                    sht.Activate
                    
                        'Unmerge line 2
                        Rows("1:2").UnMerge
                        
                        'Put some colors
                        Cells(47, 1).Interior.Color = 1111
                        ActiveWindow.ScrollRow = 1
                        ActiveWindow.ScrollColumn = 1
                        ActiveWindow.Zoom = 40
        
                        'Test if the value is cell A1 is blank/empty
                         If IsEmpty(Cells(47, 1).Value) = False Then
                            MsgBox "Cell 47 is empty"
                         End If
                         
                        'Copy the the account nb and the account name
                        accountnb = Cells(2, 8).Value
                        accountname = Cells(2, 9).Value
                        Range(Cells(3, 12), Cells(46, 12)) = accountnb
                        Range(Cells(3, 13), Cells(46, 13)) = accountname
        
                        'Copy to Master
                        Range(Cells(firstrow, firstcol), Cells(lastrow, lastcol)).Copy
                        
                        Sheets("Master").Cells((i * nbofrows) + 2, 1).PasteSpecial xlPasteValues
                        Application.CutCopyMode = False
    
                    i = i + 1
                End If
            End If
            sht.Activate
            Cells(1, 1).Activate
        End If
    Next sht
    Sheets("Master").Cells(1, 1).Activate
    end_time = Format(Now(), "dd-mmmm-yy h:mm:ss")
    Sheets("Master").Cells(1, 16).Value = "end_time"
    Sheets("Master").Cells(2, 16).Value = end_time
Application.ScreenUpdating = True

MsgBox "Operation complete."

End Sub

 

[MARK858]

What is the file extension on your workbook and what version of Excel are you using (btw change i as Integer to i as Long)?

 

[Shiro26]

What is the file extension on your workbook and what version of Excel are you using (btw change i as Integer to i as Long)?

Hi,

Thank you for your answer.
You are correct when asking me the extension... I realized that an xlsm is limited in terms of row... and this is indeed my file extension.

1) Is there another extension that I could use and run the macro even though I go beyond the 80k lines?

At the moment I am trying to create an "on error" to move the master to an xlsx and go back and pursue but I am not very good with error handling...
2) Would you have some tips on how to move the sheet called master into a new xlsx?

Unfortunately the system from which the data is extracted does not allow me to use lbound and uboud because there are blank cells and no column is filled from top to bottom...

Thank you again for your kind advises

shiro

 

[MARK858]

An Xlsm isn't limited to 65536 Rows, an Xls is.

 

[Shiro26]

An Xlsm isn't limited to 65536 Rows, an Xls is.

Well, I am not too sure what to reply to this...

I have an XLS, I open it and save it to XLSM.
Then I import my module, then I run it and it breaks.
When I go to the master sheet, and go to the bottom end, it is stuck to the 65536 row although the other sheets are at 1million+

What did I do wrong in the above stated method?
Is the part related to the sheet creation wrong?

 

[MARK858]

With the file open go to your file tab, do you have a Compatiability Mode section with a convert button?

If not what does the macro below return?

Sub vvvvv()
MsgBox "Welcome to Microsoft Excel version " & _
Application.Version & " running on " & _
Application.OperatingSystem & "!" & _
" and the file format is " & ActiveWorkbook.FileFormat
End Sub

 

[Shiro26]

With the file open go to your file tab, do you have a Compatiability Mode section with a convert button?

If not what does the macro below return?

Sub vvvvv()
MsgBox "Welcome to Microsoft Excel version " & _
Application.Version & " running on " & _
Application.OperatingSystem & "!" & _
" and the file format is " & ActiveWorkbook.FileFormat
End Sub

Thank you for your reply
Indeed, in the title it stated my file name as well as "[compatibility mode]"
Your macro returned me the message:
"MS Excel ver.15.0 [...] windows 32bit [...] file format 52"

Shiro

 

[MARK858]

Thank you for your reply
Indeed, in the title it stated my file name as well as "[compatibility mode]"

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

<img src="https://image.ibb.co/m0X7yG/pat_mode.jpg" alt="pat_mode" border="0">