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

 

[Logit]

This is one method. Paste in a regular module :

Option Explicit

Sub celCopyAdd()
    Sheets("Sheet2").Range("D7") = Sheets("Sheet1").Range("D7") + Sheets("Sheet1").Range("Q7")
End Sub

 

[akrameldaly]

thanks for your answer
This line will work for only "sheet2"
what i need is a code to work for all sheets from sheet1 to sheet31

 

[Logit]

You'll need to explain in more detail what you are needing. I understand you want the macro to work on all 31 sheets but explain in further detail what
should occur on each of those sheets.

 

[akrameldaly]

When i run the macro i need the value of the previous sheet to be added to the value in the Q column cells in the running sheet
e.g. 'sheet(N)'D7='sheet(N-1)'D7+'sheet(N)'Q7

 

[Logit]

See if this works for you :

Sub CalculateFromPreviousSheet()
    Dim ws As Worksheet
    Dim prevWs As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Index = 1 Then
            Set prevWs = ThisWorkbook.Worksheets(ws.Index - 1)
            ws.Range("D7").Value = prevWs.Range("D7").Value + ws.Range("Q7").Value
        End If
    Next ws
End Sub

 

[akrameldaly]

Thank you again

Ok now that works for only cell D7, i need it to work on all the cells in the table range "D7:G11"
i tried to write this line but it doesn't works

ws.Range("D7:D11").Value = prevWs.Range("D7:D11").Value + ws.Range("Q7:Q11").Value

it gives Run-time error '13' type mismatch

 

[Logit]

See if this works ...

Option Explicit

Sub SumAndTransferValues()
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
    Dim lastSheet As Integer
    Dim sumRange As Range
    
    lastSheet = ThisWorkbook.Worksheets.Count
    
    For i = 2 To lastSheet
        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
End Sub

 

[akrameldaly]

No, it doesn't work it tooks 5 minutes to run and at the end gives error 13 type mismatch any way thank you for your response

 

[Logit]

Hmmm ... it does run here as expected.

Can you post your wotkbook to a cloud site (DropzBox, etc) so I can download and see what is going on ?