Hi, I have a query
I have an excel file containing multiple sheets of sales invoices. I want to copy some information like client name, date, total, etc and paste it in master sheet. Currently I am using a macro to copy the information in same cell across all the sheets. To do this, I go to the cell that I want to copy from other sheets and run this macro:
Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub
The problem is that the total of invoices is not in the same cell. So I need a macro to search for "Grand Total" in each sheet and copy the cell next to it and paste it in master sheet.
I have an excel file containing multiple sheets of sales invoices. I want to copy some information like client name, date, total, etc and paste it in master sheet. Currently I am using a macro to copy the information in same cell across all the sheets. To do this, I go to the cell that I want to copy from other sheets and run this macro:
Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub
The problem is that the total of invoices is not in the same cell. So I need a macro to search for "Grand Total" in each sheet and copy the cell next to it and paste it in master sheet.