Link to home
Start Free TrialLog in
Avatar of davie jonea
davie joneaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Can any assist me with changing a macro given to me

Ive got this macro but I need to amend and dont know where to start

The clear range D18-28 is correct but I need the data from sheet called SHIFT SHEET L12-L15 and also cell L22 copied and pasted and also instead of copying X21-X24 I need data from sheet called SHIFT SHEET cells L12-L22 copied and pasted.

Any Ideas.

Thanks

Dim wsCash As Worksheet


    Set wsCash = ThisWorkbook.Worksheets("CASH SHEET")
   
    If vbYes = MsgBox("Are you sending your coins in your 700 cash bag?", vbYesNo + vbQuestion, "Sending Your Coins?") Then
        wsCash.Range("D18:D28").ClearContents
        Range("X21:X31").Copy
        wsCash.Range("D18:D28").PasteSpecial Paste:=xlPasteValues
    Else
        wsCash.Range("D18:D28").ClearContents
        Range("X21:X24").Copy
        wsCash.Range("D18:D21").PasteSpecial Paste:=xlPasteValues
        Range("Y31").Copy
        wsCash.Range("D28").PasteSpecial Paste:=xlPasteValues
    End If
    Application.CutCopyMode = False
'
End Sub

Open in new window

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

I'm not sure where L22 needs copying to.

This may be what you want

Sub x()
    Dim wsCash As Worksheet
    
    
    Set wsCash = ThisWorkbook.Worksheets("CASH SHEET")
    
    If vbYes = MsgBox("Are you sending your coins in your 700 cash bag?", vbYesNo + vbQuestion, "Sending Your Coins?") Then
        wsCash.Range("D18:D28").ClearContents
        Sheets("SHIFT SHEET").Range("L12-L15").Copy
        wsCash.Range("D18:D28").PasteSpecial Paste:=xlPasteValues
        Sheets("SHIFT SHEET").Range("L22").Copy
        wsCash.Range("L22").PasteSpecial Paste:=xlPasteValues
    End If
    Application.CutCopyMode = False
    '
End Sub

Open in new window

Avatar of davie jonea

ASKER

Nearly there.

This is what I need but dont seem to work
Any Ideas?

    Dim wsCash As Worksheet
   
   
    Set wsCash = ThisWorkbook.Worksheets("CASH SHEET")
   
    If vbYes = MsgBox("Are you sending your coins in your 700 cash bag?", vbYesNo + vbQuestion, "Sending Your Coins?") Then
        wsCash.Range("D18:D28").ClearContents
        Sheets("SHIFT SHEET").Range("M12-M22").Copy
        wsCash.Range("D18:D28").PasteSpecial Paste:=xlPasteValues
        End If
    Application.CutCopyMode = False
     Else
        wsCash.Range("D18:D28").ClearContents
        Sheets("SHIFT SHEET").Range("M12-M15").Copy
        wsCash.Range("D18:D28").PasteSpecial Paste:=xlPasteValues
        Sheets("SHIFT SHEET").Range("L22").Copy
        wsCash.Range("D28").PasteSpecial Paste:=xlPasteValues
    End If
    '
End Sub

Open in new window



You need to attach an example of the workbook.
Please find attachement. I think I may be complicating things. lets see if it can be done easier.

I have a copy and paste link from cells P45-P55 on sheet called data input into cells D18-D28 in sheet called cash sheet.

What I need it to do is ask if sending coins in 700 bag as now and if answer is YES then nothing needs to happen but if NO then I need data from cells D22-D27 deleting as in clear contents so still adds up the total in D28. Afterwards when I delete the data from the other cells ready for new input I need the formulas in D22-D27 putting back in incase I do send coins next time.

NEWCUBICANDSHERESHEET sunday.xlsm 
D12 to D18 contains formulas which your code will delete
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Pleased to help.