How to ignore cell if its filled?

plshelpimpoor

New Member
Joined
Jul 14, 2019
Messages
4
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
Code:
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
 
Upvote 0
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.
Code:
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"


 
Upvote 0
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.
 
Upvote 0
I'm sorry, but I'm not a VBA programmer. I do not have enough free time to study it now.
Regards
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top