• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidation of sheets

Neeraj Dhiman

New Member
Hi There,

I have created a time sheet however while consolidating the sheet data in main sheet following difficulties/changes i am facing.
1. If there is no data in tab it will take the heading and paste in main sheet
2. In time sheet i have to keep my cursor under the start button column (If it possible to hit the start button and it will fill only current row cell. Also same for the end button

Thanks in advance

Unable to attached file please give suggestion in comments
 

Neeraj Dhiman

New Member
Please find the code i am using

Code:
Sub Copy_Sheets_To_Master()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim Lastrowa As Long
Dim Lastrowd As Long
Lastrow = 3
Lastrowd = 3


    For i = 2 To Sheets.Count
       
        ans = Sheets(i).Name
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        Sheets(i).Range("A3:I" & Lastrowa).Copy Sheets("Time Study Data").Range("A" & Lastrow)
        Lastrowd = Sheets("Time Study Data").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("Time Study Data").Range("D" & Lastrow & ":D" & Lastrowd).Value = ans
        Lastrow = Sheets("Time Study Data").Cells(Rows.Count, "A").End(xlUp).Row + 1
       
    Next
    Application.ScreenUpdating = True
    MsgBox "Consolidate File is ready !!"
   
End Sub
 
Last edited by a moderator:
Top