Copying Data from WorkSheets and Pasting them to a different Worksheets/Workbook

Hello all ,
I have about 50 excel workbooks and each of these work books has about 20-25 spreadsheets. I am creating a database and i need to transfer all those data in access. I made a sample workbook for you guys. Plus i have the macro code in it as well. When i run the macro, for the 1st spreadsheets it runs fine. When it goes to the next spreadsheets, its over lapping previous date field with the current date field. I need some help with that.

Here is the workbook and it has the macro code in it too. If you run the macro you will easily see what i am talking about.

Thanks in advance!

sample.zip

Rahyan,
The code does not do much, excep to select and copy in to active sheet. You are copying from format sheeet to active sheet.
I do not know what you are trying to do. Part of the lines on function seems to be copied from Ron's code. Where are these 25 spredsheets? Are you trying to consolidate these sheets in to one on the same with workook?

I just checked it again and its actually copying the data from 1.3 - 1.9 and pasting it into format sheet. I am not sure who this Ron person is to be honest. and the sample file i uploaded its a dummy file. not the actual file i am working with. its all happening in one workbook. I am not pasting the file into diff workbook.

Okay,
You just want to merge all the data from each sheet in to the format. I can see row 4 to 25 be copied in to format
How do the dates in each sheet A1 will be copied in to format? I will write you a code if you could answer this question.

For an example, If you are copying the data from 1.3 sheet, on date row next to each data fields it should show 1/3/2012 since the date in 1.3 is 1/3/2012. and it will follow the same when it moves to next sheet (1.5)

Date: 1/3/2012
this date appears on a single cell, but the data to be copied occupies from rows 5 to 25. Where exactly on the format sheet this date will be copied. Is it on row 5, 6, or 25, or in all rows from 5-25 and in which column as well?

There you go!

Let me send you an excel file and show you exactly how it should look! 1 sec!

Okay,
Few things not sure, for e.g I am not sure if you want to copy item and where the heading on the format should be(row)?
but see the attached and can easily tweak it.
Sorry! I am unable to attach a book (problem with the site).
Plase try this code on the first attached book, not the latest you sent and let me know

Sub ConsolidateSheets1()

 Dim ms As Worksheet, ws As Worksheet, LR As Long, i As Long
 
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0
    
    On Error Resume Next
    
    If Not Evaluate("ISREF(format!A1)") Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "format"
    Else
        Set ms = Sheets("format")
        
        Sheets("format").Range("A2:G" & Rows.Count).ClearContents
    End If
     
     Worksheets(1).Range("A4:G4").Copy ms.Range("B1")
 
 For Each ws In ActiveWorkbook.Sheets
     With ws
         If ws.Name <> "format" Then
             LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
            .Range("A5:F" & LR).Copy ms.Range("B" & Rows.Count).End(xlUp)(2)
             Rng = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 4
             ms.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(Rng) = .Cells(1, 1)
         End If
     End With
     Application.CutCopyMode = 0
 Next ws
 
    With ms
     '.Range("C2:C" & Rows.Count - 5).NumberFormat = "mm/dd/yyyy"
     .Cells.Columns.EntireColumn.AutoFit
    End With
      Application.ScreenUpdating = 1
      Application.DisplayAlerts = 1
End Sub

Thank you so Much! it worked just like how i wanted! just had one question. Are those dates coming from whats on each spreadsheets?

Rayhan,
Yes, column A is the date in A1 from each sheet.