macro to collect data from multiple sheets to one

[makinmomb]

I have a file that has sheet names as follows

01.09.2018
02.09.2018
and so on

I want to append data of all sheets as one sheet namely on Sheet1

Searching on multiple sheets is lengthy , i also do know you can use
CTRL F and choose Worksheet , which not helps according to the data
i am working on

 

[makinmomb]

Append data macro if i were to the motto of the what the macro will do
, find next sheet , append data as is on sheet1 , find again next sheet , so
the macro will do that like around 30 or 31 times depending on the month type

 

[makinmomb]

Avoiding to copy pasting for 30 to 31 times

 

[JackDanIce]

Try:

Sub Macro1()

    Dim w               As Long
    Dim w1              As Worksheet: Set w1 = Sheets("Sheet1")
    Dim Lasts(1 To 2)   As Long
    
    Application.ScreenUpdating = False
    
    For w = 1 To Worksheets.Count
        With Sheets(w)
            Lasts(1) = LastRow(Sheets(w)): Lasts(2) = LastCol(Sheets(w))
            If .Name <> w1.Name Then w1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Lasts(1), Lasts(2)).Value = .Cells(1, 1).Resize(Lasts(1), Lasts(2)).Value
        End With
    Next w
    
    Application.ScreenUpdating = True
    
    Set w1 = Nothing
    Erase Lasts
    
End Sub

Private Function LastRow(ByRef wks As Worksheet) As Long

    With wks
        LastRow = .Cells.Find(What:="*", after:=.Cells(1, 1), LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows).Row
    End With
    
End Function


Private Function LastCol(ByRef wks As Worksheet) As Long

    With wks
        LastCol = .Cells.Find(What:="*", after:=.Cells(1, 1), LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns).Column
    End With
    
End Function

Replace all of your existing code in a module with above

 

[makinmomb]

It did but it only picked a header as follows

TRANSROAD - 01/08/18


JOB NO:
TRANSROAD - 02/08/18


JOB NO:
TRANSROAD - 03/08/18


JOB NO:
TRANSROAD - 04/08/18


JOB NO:
TRANSROAD - 06/08/18

 

[makinmomb]

It needs to pick everything including formats of each cell box including the header it picker which is a merged cell

 

[JackDanIce]

I can't see your screen, no idea what the start row is or the end column is, can't see where in your posts it says to copy the format

I want to append data of all sheets as one sheet namely on Sheet1

Append data is not the same as copy everything and paste to sheet1

The code finds the last used row and column of each sheet and then makes a Range from A1 to cell(Lastcolumn & Lastrow) and copies it to the first blank empty row in column A of sheet1.

No idea why it's not returning the entire sheet, as stated, this is based on the information you provided or didn't so had to use assumptions.

 

[makinmomb]

In case this helps

first row merged cell from a to i , below it is data , column length , a=11.71 , b=58 , c=12 , d=11.29 , e=14.57 , f=14.57 , g=16 , h=14.57 , i=32.29

 

[JackDanIce]

In case this helps

VBA does not work well with merged cells, so more detail that was omitted when initial problem described.

Have you tried to record a macro and analyse resulting code?

You do understand only you can see your spreadsheet and your PC screen? You want help but you're describing the problem with very little precise or specific information so difficult to know what to suggest.