Copy for cells from all sheets and paste in to 1 sheet

[gssachin]

hi,

I have excel workbook for 90 sheets and each sheet "B5" cell contain employee number and "E185" cell contain "Amount"

I want to copy emp number & amount from all worksheets to 1 master sheet in column a2 and column b2

thanks in advance...

 

[My Aswer Is This]

Assuming you have a sheet named "Master"
Which is the first Worksheet in your Workbook

Try using this script:

Sub Copy_Name_Amount()
'Modified 4/6/2021  2:20:08 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 2 To Sheets.Count
        With Sheets(i)
            .Range("B5").Copy Sheets("Master").Cells(Lastrow, 1)
            .Range("E185").Copy Sheets("Master").Cells(Lastrow, 2): Lastrow = Lastrow + 1
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[gssachin]

It's work for me...

Thank you so much...

 

[My Aswer Is This]

It's work for me...

Thank you so much...

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.