Merging data - starting row x instead of first row

Shiro26

Board Regular
Joined
Oct 2, 2015
Messages
82
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

Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You first calculate
Code:
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:
Code:
Sheets("Master").Cells((i * nbofrows) + 1, 1).PasteSpecial 'xlPasteValues
for first case (i=0) probably there is nothing to copy in
Code:
[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?)
Code:
Cells((i * nbofrows) + 1, 1)
 
Upvote 0
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
Code:
                        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

Code:
'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
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
An Xlsm isn't limited to 65536 Rows, an Xls is.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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?

Code:
Sub vvvvv()
MsgBox "Welcome to Microsoft Excel version " & _
Application.Version & " running on " & _
Application.OperatingSystem & "!" & _
" and the file format is " & ActiveWorkbook.FileFormat
End Sub
 
Upvote 0
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?

Code:
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
 
Upvote 0
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">
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top