Carring unused data forward to another sheet however starting in a nominated cell

[rustybenson]

Hi I have four colums of data on sheet 1 that represents first day of the month 1/5/2011 (AUST Date) with a list of shipments that have sales put uo to them. Each day the list is added to i.e. new shipments arrive and each day we seel from the oldest shipmet until it is sold and we move to the next shipment The days shipment data as below can occupy rows 13-26 Colum B-E

B c d E
Shipment No Litres On Shipment Litres sold From Shipment Litres Left to sell
13 123456 10000 10000 0
14 123457 15000 15000 0
15 123458 10000 5000 5000
16 123459 12000 0 12000

What I want to be able to do is the shipments that are exhausted (sold) are eliminated from the next days sheet and the ones that are not exhausted or have litres left to sell are transfered to sheet 2 (representing 2/5/2011) however starting in B13.
We can then add the next days shipments under the unused ones from yesterday.
So basically the shipments unused (or partialy used) from day one sheet are carried forward to day two sheet but starting in cell B13. Above example would become this next day sheet
B c d E
Shipment No Litres On Shipment Litres sold From Shipment Litres Left to sell
13 123458 10000 5000 5000
14 123459 12000 0 12000


This happens day to day untill month ends.
For the new month we carry the unsed shipments over manually.

I Hope this makes sense and I hope somene can help.
As always any help is appreciated.
Regards
Rusty

 

[sijpie]

Yes a simple macro can fix this.

You would run the macro at the end of the previous or start of the next day. The easiest is that it copies a template sheet, gives it the name of the new day and copies the lines across that have a value in column E.

As there are relatively few lines to deal with (20) the macro can be kept very simple.

Option Explicit
Sub NextDay()
    Dim NewDate As String
    Dim wsCurrent As Worksheet
    Dim iOld As Long, iNew As Long
    
    Set wsCurrent = ActiveSheet
    NewDate = InputBox("Date for next sheet (dd-mm-yy)", "Provide name for next sheet")
        ' replace any / characters a user might have entered accidently
    NewDate = Application.WorksheetFunction.Substitute(NewDate, "/", "-")
    
    Worksheets("Template").Copy after:=wsCurrent
    ActiveSheet.Name = NewDate
    iOld = 13
    iNew = 13
    
    Do While wsCurrent.Range("B" & iOld).Value <> vbNullString
        If wsCurrent.Range("E" & iOld).Value > 0 Then
            ActiveSheet.Range("B" & iNew & ":D" & iNew).Value = _
                wsCurrent.Range("B" & iOld & ":D" & iOld).Value
            ActiveSheet.Range("E" & iNew).Formula = "=C" & iNew & "-D" & iNew
            iNew = iNew + 1
        End If
        iOld = iOld + 1
    Loop
    
End Sub

 

[rustybenson]

Re: Carrying unused data forward to another sheet however starting in a nominated cell

Thanks for the Macro
I get the following in debug
Sub NextDay()
Dim NewDate As String
Dim wsCurrent As Worksheet
Dim iOld As Long, iNew As Long

Set wsCurrent = ActiveSheet
NewDate = InputBox("Date for next sheet (dd-mm-yy)", "Provide name for next sheet")
' replace any / characters a user might have entered accidently
NewDate = Application.WorksheetFunction.Substitute(NewDate, "/", "-")

Worksheets("Template").Copy after:=wsCurrent THIS LINE BECOMES HI-LITED IN YELLOW
ActiveSheet.Name = NewDate
iOld = 13
iNew = 13

Do While wsCurrent.Range("B" & iOld).Value <> vbNullString
If wsCurrent.Range("E" & iOld).Value > 0 Then
ActiveSheet.Range("B" & iNew & ":D" & iNew).Value = _
wsCurrent.Range("B" & iOld & ":D" & iOld).Value
ActiveSheet.Range("E" & iNew).Formula = "=C" & iNew & "-D" & iNew
iNew = iNew + 1
End If
iOld = iOld + 1
Loop

End Sub


There is a line hilighted in yellow.
My sheets are named from shhet1 to sheet 35 (incase I have to do two in a day)
The data I explained is the only part of the sheet that needs to move as described. There is data from a1 to n30
Regards
Rusty

 

[sijpie]

As I explained - the macro wants to copy a template sheet (called Template) that has all the headings etc .

It then only adds data from row 13 onwards.

So create a template sheet, and call it Template, and the macro should work.

The other thing is in your original mail you mention columns B trhough E, now you say A thru N. So what is in the other columns and do these need to be copied as well? (at the moment only Bx:Ex are copied if Ex > 0)

 

[rustybenson]

Hi
I can't seem to get it to work. Is it possible to send a copy of what I am doing so you can understand what I am doing?

 

[sijpie]

Easiest is to post it on a sharing website.
Here is my test file to show how it works.
oilshipments.xls

 

[rustybenson]

I still can't get you sample to work. it keeps bringing up debug
I have put my file on the site you use.
http://www.4shared.com/file/svHTOmpb/Copy_of_Port_Augusta_Balance.html

 

[sijpie]

First of all, I mentioned it a few times, I was expecting that you had renamed a blank template sheet as 'Template', because the macro looks for this. That is why the macro won't run. If you request help, then read the instructions properly please.

Second in your first post you mention that in column E the remaining oil volume of each shipment will be stored. So my macro was looking for rows with a value in column E, to transfer these to the next sheet. But in the Excel sheet you have posted column E doesn't seem to be the correct column to read from. I am assuming it should be the sum of column E,F & G that should be less or equal to the Shipment Volume. Is that correct?

I have amended the macro to fit your sheet, and I have changed your sheet.

Amendments to the sheet:

• Renamed tabs to dates to make it easier to use
• Deleted all the tabs of future dates. A new tab will be created (from Template) when you press the button. Keeps your workbook tidy.
• Renamed one of the sheets 'Template. This is used to create anew tab from.
• Put the button in the active sheet to run the macro

have a go and let me know if OK.
Port_Augusta_Balance.xlsm

 

[rustybenson]

Hi
Thanks for your help it appears to be working fine, I will test over the next few days.
My initial requests were on the run trying to build the sheet I finally sent you. I thought once i could get the info I wanted to change I could build the rest around it. I do appoligize for the "lack" of info.
The only question I have is starting up a new month. How do I delte the current info (say in this case a couple of days ) and get the sheet to operate with one sheet showing for the first day of the month ( which may not always be the 1st day of the month. We operate on 544)
Again thanks alot for your help

 

[sijpie]

Just create a new sheet for the starting day you want. Then delete all previous sheets, and enter the measured data in the fields. Nothing special.