Copy - Paste (Value) does not work with macro

Question

I have a workbook which must be reset every month (using a macro button).  On some sheets, I have a formula that determines what information must be carried forward to the next month.

in Row 37, Columns D, L, O, and R, I use the following to determine what is carried forward:

D37 has formula =IF(Month!$B$2=31,BWF!$R$36,IF(Month!$B$2=30,BWF!$R$35,IF(Month!$B$2=29,BWF!$R$34,BWF!$R$33)))

The Month tab compares this month against the next month to determine how many days are in the current month (July would be 31, Jan would be 31, etc).

For November (30 days), D37 would show the value from D35,etc as that is the value I need to carry forward to the next workbook.  This is the value that must be carried to the next month as a value into Row 5, Column D, etc for L, O, and R

When I manually copy and paste the value into Row 5 into the respective column, it does work.  When I use a macro, the value is not carried carried over to be  saved?

...bunch of code

                If ws.Range("A1").Value = "*" Then

                    ActiveSheet.Unprotect Password:="itsasecret"

                    If Range("D6").Locked = False Then

                    Range("D37").Select

                    Selection.Copy

                    Range("D5").Select

                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                        :=False, Transpose:=False

                    End If

                    Range("L37").Select

                    Selection.Copy

                    Range("L5").Select

                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                        :=False, Transpose:=False

                    Range("R37").Select

                    Selection.Copy

                    Range("R5").Select

                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                        :=False, Transpose:=False

                    If ws.Range("A2").Value = "ny" Then

                    Range("O5").Value = "0"

                    Else

                    Range("O37").Select

                    Selection.Copy

                    Range("O5").Select

                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                        :=False, Transpose:=False

                    End If

                    ActiveSheet.Protect Password:="itsasecret", DrawingObjects:=True, Contents:=True, Scenarios:=True

... rest of code

what's missing?

kala

Answer 1

Id get rid of all you select copy and paste special. All u need is this.

Range("D5").Value = Range("D37").Value

Answer 2

Explanation of the A1=* and the check for a locked cell:

The original code was longer being written for each worksheet.  I changed the worksheet layout so I could have four sets of code and the easiest way for me to determine which code would run on the worksheet was a "flag" in A1.  The code goes through each visible worksheet and using If/Then, the appropriate portion of the code would do it's thing.

The check to see if a cell (Column) is locked was to reduce the amount of hidden columns.  On each worksheet, there may be columns that are not used depending on the specific situation.  In order to prevent remaking the workbook, or worksheet depending on multiple sites and scenarios, the best thing was to make four generic worksheets and lock the columns that would not be used.  In my first mock up, I had the columns hidden, and the check was for .entirecolumn.hidden = true..., but the layout of the worksheet changed resulting in pages being scaled down in ranges from 49% up to 100%.  To eliminate this, the most common columns are set to visible but the cell or column is locked so that the layout and sizing is consistent when the file is saved/emailed, etc.

I looked at the code again, and I understand how it's confusing - I unlocked the worksheet then I check to see if the cell is locked... pointless.  The assumption was if the there was data in that field then there is a value in D37 that must be copied for the next month.  In hindsight, there is no need to check if those fields are locked because the default value of 0 would be copied to the next month.  I"ll remove the check to see if the cell is locked in the copy paste value portion of the code, and try the balance of your code.

thank you,

kala

Answer 3

Hi,

I would re-write each section of your code as below to do away with any selecting then it will work IF

There is an asterisk in A1

Cell D6 isn't locked. Why you're actually testing for this isn't clear but I assume you have a reason.

EDIT. This business of checking if D6 is locked is confusing me, you aren't prehaps misunderstanding what the 'Locked' property of a cell is?

By default every cell in the worksheet is 'Locked' and you can see the locked status by right clicking the cell, format, protection. Being 'Locked' has no affect until a worksheet is protected.

If ws.Range("A1").Value = "*" Then

    ActiveSheet.Unprotect Password:="itsasecret"

    If Range("D6").Locked = False Then

        Range("D37").Copy

        Range("D5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                         :=False, Transpose:=False

       End If

End If