Macro for daily report

[akrameldaly]

Hello,

I have a daily report on Excel which follows production figures and i need to make a macro to save time each day, the workbook represents a whole month and comprises of different worksheets each work sheet represents a day in the month starting from day 1 to day 31



Sheet 1

B4	D4	E4	F4	G4	Q4
					NO. OF HT
B7	2490	525	141	360	21
B8	2515	397	93	660	21
B9	13348	94	94	94	21
B10	11345	311	118	142	20
B11	3964	339	164	339	26

Sheet 2

	D4	E4	F4	G4	Q4
					NO. OF HT
B7	2511	546	162	381	21
B8	2536	418	114	681	21
B9	13369	115	115	115	21
B10	11366	332	139	163	20
B11	3985	360	185	360	26

Sheet 3

B4	D4	E4	F4	G4	Q4
					NO. OF HT
B7	2531	566	182	401	21
B8	2556	438	134	701	21
B9	13389	135	135	135	21
B10	11386	352	159	183	20
B11	4005	380	205	380	26

what i do is editing the formula for each worksheet as follows

D7 in sheet2 = 'sheet1'D7+Q7

I need to edit a macro to automate this work and to save time

 

[akrameldaly]

i have uploaded the file in google drive and dropbox

https://docs.google.com/spreadsheets/d/1drLJjsgiCiFANret_XrtW-CDK1yMsw1S/edit?usp=sharing&ouid=111202079155029599903&rtpof=true&sd=true

Dropbox

www.dropbox.com

 

[Logit]

Try this macro, that presumes you will always have 21 sheets.

Option Explicit

Sub SumAndTransferValues()
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
    Dim lastSheet As Integer
    Dim sumRange As Range
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    lastSheet = ThisWorkbook.Worksheets.Count
    
    For i = 2 To 21
        Set ws = ThisWorkbook.Worksheets(i)
        
        For j = 7 To 11
            ws.Cells(j, 4).Value = ws.Cells(j, 4).Value + ThisWorkbook.Worksheets(i - 1).Cells(j, 4).Value
            ws.Cells(j, 5).Value = ws.Cells(j, 5).Value + ThisWorkbook.Worksheets(i - 1).Cells(j, 5).Value
            ws.Cells(j, 6).Value = ws.Cells(j, 6).Value + ThisWorkbook.Worksheets(i - 1).Cells(j, 6).Value
            ws.Cells(j, 7).Value = ws.Cells(j, 7).Value + ThisWorkbook.Worksheets(i - 1).Cells(j, 7).Value
        Next j
        
        For j = 7 To 11
            ws.Cells(j, 17).Value = Application.WorksheetFunction.Sum(ws.Range(ws.Cells(j, 4), ws.Cells(j, 7)))
        Next j
    Next i
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

 

[akrameldaly]

i have uploaded the file in google drive and dropbox

https://docs.google.com/spreadsheets/d/1drLJjsgiCiFANret_XrtW-CDK1yMsw1S/edit?usp=sharing&ouid=111202079155029599903&rtpof=true&sd=true

Dropbox

www.dropbox.com

 

[Logit]

It works here.