How to ignore cell if its filled?

[plshelpimpoor]

So I have two worksheets.

Work sheet 1 has monday tuesday wednesday etc. Week's data.

In Work sheet 2, i want the sum of entire week to fill cell A1 (week 1). In second week, cell A2(week 2). And so forth.


Now in A1, i wrote =worksheet1!sum(cell). The issue is, as i update the worksheet in worksheet 1 every day, the A1 data will be overwritten. Eventually all weeks have the same data.


I have thought about writing VBA script that fetches sum of cells end of week and populate A1, next week A2 etc. But how can I write in the script so that previous weeks data is not overwritten in worksheet 2?

 

[navic]

Sheet1, if we mean that in A20:E20 are the sum of each week.
Copy one of these two macros below, into the standard module.
After entering the data, start the VBA macro by button or Alt+F8.
VBA copies the specified range to Sheet2, each new copy sets in a new row.

Sub CopyToAnotherSheet()
'copy without transpose
Dim NextRow As Long
Dim cpRng As Range

Application.CutCopyMode = False
Application.ScreenUpdating = False

    Set cpRng = ActiveSheet.Range("A20:E20")
    NextRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A20:E20").Copy
        Sheets("Sheet2").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=False

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub
'---------------------------------------------------------
Sub CopyToAnotherSheet2()
'copy and transpose
Dim NextRow As Long
Dim cpRng As Range

Application.CutCopyMode = False
Application.ScreenUpdating = False

    Set cpRng = ActiveSheet.Range("A20:E20")
    NextRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A20:E20").Copy
        Sheets("Sheet2").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

 

[plshelpimpoor]

Sheet1, if we mean that in A20:E20 are the sum of each week.
Copy one of these two macros below, into the standard module.
After entering the data, start the VBA macro by button or Alt+F8.
VBA copies the specified range to Sheet2, each new copy sets in a new row.

Sub CopyToAnotherSheet()
'copy without transpose
Dim NextRow As Long
Dim cpRng As Range

Application.CutCopyMode = False
Application.ScreenUpdating = False

    Set cpRng = ActiveSheet.Range("A20:E20")
    NextRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A20:E20").Copy
        Sheets("Sheet2").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=False

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub
'---------------------------------------------------------
Sub CopyToAnotherSheet2()
'copy and transpose
Dim NextRow As Long
Dim cpRng As Range

Application.CutCopyMode = False
Application.ScreenUpdating = False

    Set cpRng = ActiveSheet.Range("A20:E20")
    NextRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A20:E20").Copy
        Sheets("Sheet2").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

Hello

Thank you so much for answering. Was in crotia last month, lovely country.

Your code from first set is giving error on line 9. Saying subscription out of range

"Sheets("Sheet2").Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=False"

 

[plshelpimpoor]

S
[/code]

Sorry I can't edit post so I am quoting you again. Was able to fix error. There was typo.

However another thing is, you have provided say range a20:e20. But I have 27 cells whose data I need.

Below is range I need to fetch data every Sunday from:
B82,D82,F82,H82,J82,L82,N82,P82,R82,B92,D92,F92,H92,J92,L92,N92,P92,R92,B104,D104,F104,H104,J104,L104,N104,P104

I would like B82 in C3, D82 in D3, F82 in F3 for week 1. For second week, B82 in C4, D82 in D4, F82 in F4 and so forth.


B82, D82 and so forth have already been totaled from 7 days in sheet 1.

 

[navic]

I'm sorry, but I'm not a VBA programmer. I do not have enough free time to study it now.
Regards