Hello,

I am having an issue in getting a little macro I wrote.
The spreadsheet originally contains 60 sheets, but I added 2 more, namely "Model" and "Database".

I need to copy data from each of the 60 original sheets into Database. All sheets are formatted exactly in the same way (number or columns / rows), but unluckily those include headers which I need to get rid of (I cannot delete rows once I imported all data as the "Database" worksheet will be formatted with xyz formula; for the same reason Ranges must be tailored to each individual sheet).

There is a sheet which has header + 1 single row, hence I cannot use Range("A2").End(xlDown) etc as it won't work.
My biggest issues seems to be that my macro won't work if any cell in the given range is selected prior to the macro.


Sub tgr()

    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim wsModel As Worksheet

    Set wsDest = Sheets("Database")
    Set wsModel = Sheets("Model")
    
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = Worksheets(3).Name Then
        Set rg = ws.Cells(1, 1).CurrentRegion
            rg.Copy
            wsDest.Cells(1.1).PasteSpecial xlPasteValues
        ElseIf ws.Name <> wsDest.Name And ws.Name <> wsModel.Name And ws.Name <> Worksheets(3).Name Then
            rg.Select
            Selection.Offset(1).Resize(Selection.Rows.Count - 1).Copy
            wsDest.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next ws

End Sub
Any help would be greatly appreciated!