Small edit / addition to existing working code

[ipbr21054]

Good Afternoon,
I have the working code in use as supplied below.
When i press my transfer button cell values are coppied from one worksheet to the next sheet.
I then manually select from a drop down bow in cell B1 the month & in cell B2 the year.

I would like to add the above to the working code below so not only does it copy from worksheet to worksheet but also put JUNE 2019 in the cells mentioned.

I would also then use the code in next months worksheets but changed JUNE in the code to JULY

Please advise,many thanks.

Private Sub CommandButton1_Click()  Sheets("EXPENSES (3)").Range("D4").Value = Sheets("EXPENSES (2)").Range("D30").Value
  Sheets("EXPENSES (3)").Range("F4:K4").Value = Sheets("EXPENSES (2)").Range("F30:K30").Value
  Sheets("EXPENSES (3)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("EXPENSES (3)").Range("K32").Value <> Sheets("EXPENSES (2)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
End Sub

 

[sykes]

Hi there

Forgive me if I've not understood you fully...

If I've got it right, then this should do what you wish:

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cl As Range


  With Sheets("EXPENSES (3)")

        .Range("D4").Value = Sheets("EXPENSES (2)").Range("D30").Value & " JUNE 2019"
        .Range("F4:K4").Value = Sheets("EXPENSES (2)").Range("F30:K30").Value
  

Set rng = .Range("F4:K4")

    For Each cl In rng
        cl.Value = cl.Value & " JUNE 2019"
    Next

  .Activate
    ActiveSheet.Range("A5").Select
  If .Range("K32").Value <> Sheets("EXPENSES (2)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
    End With
    
End Sub

....I've cleaned the code up a little - to try & keep it fairly compact. Hope you don't mind.

I'm also wondering, whether we couldn't use the month & year from your dropdown boxes, instead of having to put these values into your code manually, each time they change. As long as these dropdowns contained the desired values, it should work. On which sheet are the dropdowns in B1 & B2?

 

[ipbr21054]

Hi,
Will check this out once home.

I complete say worksheet expenses 1 then press the button to copy values from the cells which are at the bottom of true sheet over to the top of the next sheet which is called expenses 2.
I then start to add values in cells working my way down the sheet again and like before when I get to the bottom I press the button and these values are copied to the top of the next sheet which is then called expenses 3.

This keeps going until I reach the end of the month.

Regarding the date and year it’s just so I don’t manually have to do if after the code has take the values from bottom to top as shown above. Just thought when I press the button let the code copy and paste the values and also select the month & year for me.

 

[sykes]

OK. We'll see how you get on with it, at home.
Hope it does what you're wanting it to.

 

[ipbr21054]

Hi,
I applied that code to my worksheet called EXPENSES 2
When i press my command button this should happen.

EXPENSES 2 COPY VALUE IN CELL D30 AND PASTE IN WORKSHEET 3 CELL D4
EXPENSES 2 COPY VALUE IN CELL F30 AND PASTE IN WORKSHEET 3 CELL F4
EXPENSES 2 COPY VALUE IN CELL G30 AND PASTE IN WORKSHEET 3 CELL G4
EXPENSES 2 COPY VALUE IN CELL H30 AND PASTE IN WORKSHEET 3 CELL H4
EXPENSES 2 COPY VALUE IN CELL I30 AND PASTE IN WORKSHEET 3 CELL I4
EXPENSES 2 COPY VALUE IN CELL J30 AND PASTE IN WORKSHEET 3 CELL J4
EXPENSES 2 COPY VALUE IN CELL K30 AND PASTE IN WORKSHEET 3 CELL K4

Also
On worksheet 3 cell B1 have the month entered or selected from the drop down list that is currently in use so JUNE
On worksheet 3 cell B2 have the year entered or selected from the drop down list that is currently in use so 2019


With the supplied code as in post #2
When i press the command button i see it transfer the value & date & year into the cell D4 etc etc on the next worksheet.
So lets say cell D4 is now like this 498.64 june 2019

Where cell D4 should just show 498.64

Where cell B1 should show JUNE

Where cell B2 should show 2019

Thanks

 

[ipbr21054]

This code does what i need & everything in its correct place but can you take a look please.

Private Sub CommandButton1_Click()  Dim rng As Range
Dim cl As Range




  With Sheets("EXPENSES (3)")


        .Range("D4").Value = Sheets("EXPENSES (2)").Range("D30").Value
        .Range("F4:K4").Value = Sheets("EXPENSES (2)").Range("F30:K30").Value
        .Range("B1") = "JUNE"
        .Range("B2") = "2019"
  


Set rng = .Range("F4:K4")


    For Each cl In rng
        cl.Value = cl.Value
    Next


  .Activate
    ActiveSheet.Range("A5").Select
  If .Range("K32").Value <> Sheets("EXPENSES (2)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
    End With
    
End Sub

 

[sykes]

OK - if that code's doing what you want, then we can take a little more out, to simplify it:

Private Sub CommandButton1_Click()  
Dim rng As Range
Dim cl As Range




  With Sheets("EXPENSES (3)")


        .Range("D4").Value = Sheets("EXPENSES (2)").Range("D30").Value
        .Range("F4:K4").Value = Sheets("EXPENSES (2)").Range("F30:K30").Value
        .Range("B1") = "JUNE"
        .Range("B2") = "2019"
 

  .Activate
    ActiveSheet.Range("A5").Select
  If .Range("K32").Value <> Sheets("EXPENSES (2)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
    End With
    
End Sub

 

[sykes]

This is what I was thinking about, with regard to getting the code to put month & year in for you.
It'll take the current month and put it into B1, and the current year, and put it into B2 - so you don't have to bother editing your code each time the month changes. Not sure if it's what you need, if not, just scrap that bit.

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cl As Range


  With Sheets("EXPENSES (3)")


        .Range("D4").Value = Sheets("EXPENSES (2)").Range("D30").Value
        .Range("F4:K4").Value = Sheets("EXPENSES (2)").Range("F30:K30").Value
        .Range("B1") = Format(Date, "MMMM")
        .Range("B2") = Format(Date, "YYYY")
  

  .Activate
    ActiveSheet.Range("A5").Select
  If .Range("K32").Value <> Sheets("EXPENSES (2)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
    End With
    
End Sub

 

[ipbr21054]

Hi,
I have now changed the code to what you advised in your last post as it makes sense to do so.
Thanks very much.

 

[sykes]

Pleasure! Thanks for the feedback.