Could you check my code please

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
Evening,

I have a worksheet called G INCOME
At the end of the month i press a command button which creates a pdf file of that months takings & also copies the total profit & mileage for that month & pastes it on the worksheet called G SUMMARY.
The pdf side of things work fine.

The profit / mileage is the issue.
On the G SUMMARY worksheet are the months April May June through to April next year.
April D5:E5
May D6:E6
June D7:E7

April D17:E17

Ive just done Aprils paperwork & pressed the command button but as opposed to the figures etc going in This years April D5:E5 its put into next years April D17:E17

Below is the code in use.

Command Button.

VBA Code: 
Private Sub TransferButton_Click()
    
    Call INCOMETRANSFER
    
    If PDFExists Then
'       Do nothing
    Else
        Call SUMMARYTRANSFER
    End If
    INCOMEMONTHYEAR.Show
End Sub

G SUMMARY worksheet code.

Code: 
Private Sub SUMMARYTRANSFER()
    Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fRow As Long
    Dim sh As Worksheet
    Dim ws As Worksheet
    Dim strDate As String

    Set ws = Sheets("G INCOME")
    Set sh = Sheets("G SUMMARY")
    stFnd = ws.Range("A3").Value
    strDate = ws.Range("A5").Value
    With sh
        Set rFndCell = .Range("C5:C17").Find(stFnd, LookIn:=xlValues)
        If Not rFndCell Is Nothing Then
            fRow = rFndCell.Row
            If CDate(strDate) > CDate("05/04/2020") Then
                sh.Cells(fRow, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow, 5).Resize(, 1).Value = ws.Range("E31").Value
            Else:
                sh.Cells(fRow - 12, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow - 12, 5).Resize(, 1).Value = ws.Range("E31").Value
            End If
            MsgBox "TRANSFER TO SUMMARY SHEET ALSO COMPLETED", vbInformation + vbOKOnly, "SUMMARY TO TRANSFER SHEET COMPLETED MESSAGE"
        Else
            MsgBox "DOES NOT EXIST", vbCritical + vbOKOnly, "SUMMARY TO TRANSFER SHEET FAILED MESSAGE"
            Range("A5").Select
        End If
        Range("A3").ClearContents
        Range("B3").ClearContents
        Range("C3").ClearContents
        Range("A5:B30").ClearContents
        Range("A5:A30").NumberFormat = "@"
        Range("A5").Select
        ActiveWorkbook.Save
    End With
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
ipbr21054 said:
Evening,

I have a worksheet called G INCOME
At the end of the month i press a command button which creates a pdf file of that months takings & also copies the total profit & mileage for that month & pastes it on the worksheet called G SUMMARY.
The pdf side of things work fine.

The profit / mileage is the issue.
On the G SUMMARY worksheet are the months April May June through to April next year.
April D5:E5
May D6:E6
June D7:E7

April D17:E17

Ive just done Aprils paperwork & pressed the command button but as opposed to the figures etc going in This years April D5:E5 its put into next years April D17:E17

Below is the code in use.

Command Button.

VBA Code: 
Private Sub TransferButton_Click()
   
    Call INCOMETRANSFER
   
    If PDFExists Then
'       Do nothing
    Else
        Call SUMMARYTRANSFER
    End If
    INCOMEMONTHYEAR.Show
End Sub

G SUMMARY worksheet code.

Code: 
Private Sub SUMMARYTRANSFER()
    Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fRow As Long
    Dim sh As Worksheet
    Dim ws As Worksheet
    Dim strDate As String

    Set ws = Sheets("G INCOME")
    Set sh = Sheets("G SUMMARY")
    stFnd = ws.Range("A3").Value
    strDate = ws.Range("A5").Value
    With sh
        Set rFndCell = .Range("C5:C17").Find(stFnd, LookIn:=xlValues)
        If Not rFndCell Is Nothing Then
            fRow = rFndCell.Row
            If CDate(strDate) > CDate("05/04/2020") Then
                sh.Cells(fRow, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow, 5).Resize(, 1).Value = ws.Range("E31").Value
            Else:
                sh.Cells(fRow - 12, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow - 12, 5).Resize(, 1).Value = ws.Range("E31").Value
            End If
            MsgBox "TRANSFER TO SUMMARY SHEET ALSO COMPLETED", vbInformation + vbOKOnly, "SUMMARY TO TRANSFER SHEET COMPLETED MESSAGE"
        Else
            MsgBox "DOES NOT EXIST", vbCritical + vbOKOnly, "SUMMARY TO TRANSFER SHEET FAILED MESSAGE"
            Range("A5").Select
        End If
        Range("A3").ClearContents
        Range("B3").ClearContents
        Range("C3").ClearContents
        Range("A5:B30").ClearContents
        Range("A5:A30").NumberFormat = "@"
        Range("A5").Select
        ActiveWorkbook.Save
    End With
End Sub
Click to expand...
Hi,
Could you advise what date you have in ws.Range("A5") while running the code? I’m asking, perhaps, the date „if statement” is met in the code which causes such result.

regards,
Sebastian
 
Upvote 0
Morning,
Please see screen shots.

These are a general look at what you would see / do for the G INCOME & G SUMMARY page.

On a side note what if we do this ?????
Currently G INCOME cell A3 shows APRIL,What if we proceed the date like APRIL 04 MAY 05 JUNE 06 so then it would continue like JANUARY 13 FEBRUARY 14 MARCH 15 APRIL 16
APRIL 04 being first instance & APRIL 16 the second instance,I say this as the pdf files are also saved like 2020_04 APRIL 2020_05 MAY

Then it would work like this.....
On G INCOME sheet look in cell A3 to collect date, "example MAY 05"

Copy info on G INVOICE in cell D31 & E31 "example D31 £456.77 E31 123"

Go to G SUMMARY sheet & look in range C5:C17 for a match just taken from G INCOME sheet so in this case MAY 05 would be cell C6

Then paste the value from G INCOME D31:E31 to its respective cell so in this case it would be D6:E6

Make sense ?
 

Attachments

  • 6809.jpg
    6809.jpg
    125.4 KB · Views: 7
  • 6810.jpg
    6810.jpg
    167 KB · Views: 7
Upvote 0
ipbr21054 said:
Morning,
Please see screen shots.

These are a general look at what you would see / do for the G INCOME & G SUMMARY page.

On a side note what if we do this ?????
Currently G INCOME cell A3 shows APRIL,What if we proceed the date like APRIL 04 MAY 05 JUNE 06 so then it would continue like JANUARY 13 FEBRUARY 14 MARCH 15 APRIL 16
APRIL 04 being first instance & APRIL 16 the second instance,I say this as the pdf files are also saved like 2020_04 APRIL 2020_05 MAY

Then it would work like this.....
On G INCOME sheet look in cell A3 to collect date, "example MAY 05"

Copy info on G INVOICE in cell D31 & E31 "example D31 £456.77 E31 123"

Go to G SUMMARY sheet & look in range C5:C17 for a match just taken from G INCOME sheet so in this case MAY 05 would be cell C6

Then paste the value from G INCOME D31:E31 to its respective cell so in this case it would be D6:E6

Make sense ?
Click to expand...

Hi,
I've adjusted one line in your code - changed range("C5:C17") to range ("C4:C17"). I've changed that because the FIND method starts to look for APRIL, in that case, from C6. With range C4:C17 it's searching APRIL from C5 on the first appearance of the APRIL. Check it out now.

I'd like to ask you about the hardcoded part of the code in if statement (CDate("05/04/2020") ). You hardcoded date 5th April. Do you change this part of code to the 5th of the Month you make a report for? I'm asking because I can see a potential to automate that a little bit so the date is calculated automatically and get a rid of hardcoded part of the code, what do you reckon?

VBA Code: 
Private Sub SUMMARYTRANSFER()
    Dim rFndCell As Range
    Dim strData As String
    Dim stFnd As String
    Dim fRow As Long
    Dim sh As Worksheet
    Dim ws As Worksheet
    Dim strDate As String

    Set ws = Sheets("G INCOME")
    Set sh = Sheets("G SUMMARY")
    stFnd = ws.Range("A3").Value
    strDate = ws.Range("A5").Value
    With sh
        Set rFndCell = .Range("C4:C17").Find(stFnd, LookIn:=xlValues) ' .Range("C5:C17").Find(stFnd, LookIn:=xlValues)
        If Not rFndCell Is Nothing Then
            fRow = rFndCell.Row
            If CDate(strDate) > CDate("05/04/2020") Then
                sh.Cells(fRow, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow, 5).Resize(, 1).Value = ws.Range("E31").Value
            Else:
                sh.Cells(fRow - 12, 4).Resize(, 1).Value = ws.Range("D31").Value
                sh.Cells(fRow - 12, 5).Resize(, 1).Value = ws.Range("E31").Value
            End If
            MsgBox "TRANSFER TO SUMMARY SHEET ALSO COMPLETED", vbInformation + vbOKOnly, "SUMMARY TO TRANSFER SHEET COMPLETED MESSAGE"
        Else
            MsgBox "DOES NOT EXIST", vbCritical + vbOKOnly, "SUMMARY TO TRANSFER SHEET FAILED MESSAGE"
            Range("A5").Select
        End If
        Range("A3").ClearContents
        Range("B3").ClearContents
        Range("C3").ClearContents
        Range("A5:B30").ClearContents
        Range("A5:A30").NumberFormat = "@"
        Range("A5").Select
        ActiveWorkbook.Save
    End With
End Sub
 
Upvote 0
Thanks for that,overlooked it & now works.

With regards the date,
Its for the Tax Year purposes
My books are made up April 6th to April 5th.

Show me what you have & i can look.

Thanks
 
Upvote 0
ipbr21054 said:
Thanks for that,overlooked it & now works.

With regards the date,
Its for the Tax Year purposes
My books are made up April 6th to April 5th.

Show me what you have & i can look.

Thanks
Click to expand...

Happy to help?
If you set your books to April 5th I recommend to replace the following line:
VBA Code: 
If CDate(strDate) > CDate("05/04/2020") Then
with the following one and it might help you.
VBA Code: 
If CDate(strDate) > DateSerial(Year(Date()),4,5) Then
Function Year(Date()) returns the year of the current date and the DateSerial returns a date masede on provided theree parameters in the following order: Year, Month, Day (all need to be integer values)

Hope that helps you too. ?
 
Upvote 0
Ok
Two questions about this new code.
When I open the sheet for the new month I have a user form pop up. I am asked for current month and year.
I then select these from a drop down list and pressing a command button puts my selections in the cells on the worksheet so you then see say APRIL 2020

Do this have any affect on your code ?

Also in the original code you see 05/04/2020
So next year when it’s 2021 will you code recognise this ?
I ask as I’m manually changing it each time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
RTE 13 Type Mismatch message
Replies
1
Views
394
rollis13
rollis13
I
  • Solved
Can you explain this CDate code please
2
Replies
13
Views
952
ipbr21054
I
I
  • Solved
Advice to remove redundant line of code currently in use
2
Replies
10
Views
718
nemmi69
nemmi69
I
  • Question Question
Code only runs if on command button
Replies
1
Views
428
ipbr21054
I
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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