Hi all,
I am trying to write an excel program that will log ledger entries monthly, but I also need a running ledger for the year. What I have done so far is to make a workbook with 13 worksheets. One for my running ledger and one for each month. What I would like to do is write a formula that will read the month column entry in the running ledger sheet and automatically send it to the corresponding month sheet. I have tried an IF function, but it won't do more than seven months. Also, the data need to be sent to the next open row in that corresponding month. For example, if B200 = 7 (month), then it would need to be sent to B2 of the sheet named July because it would be the first entry for that month. And then series from there. Am I making any sense? Am I wishing for the impossible?
Full Version: Exporting And Filtering From Worksheet To Worksheet
"What I would like to do is write a formula that will read the month column entry in the running ledger sheet and automatically send it to the corresponding month sheet."
Have you thought about reversing this so that the entries in each month are applied to a running total?
Regards,
John
Have you thought about reversing this so that the entries in each month are applied to a running total?
Regards,
John
I have actually done a "beta" that way. However, the reverse is true to some extent. The January entries would be easy since the corresponding fields in the running ledger would start at B2 and ascend. However, when I get to February, it would have to know to follow January's entries in the running ledger. Since the if,then,else formula only allows seven parameters, I'm out of luck past July. My thoughts anyway.
So let me get this right you have a worksheet that has rows that have details with a date attributed to each row. You wish to get the month for the specified field in each row and copy that row to another sheet that contains the month name.
If this is the solution you are after this is too advanced for formula and will need to be hard coded in VBA. I have done a simular thing before and have the code that will do this. If you could explain fully what you want it to do with row and column details and maybe screen shots we can get a better idea of what you are looking for.
If this is the solution you are after this is too advanced for formula and will need to be hard coded in VBA. I have done a simular thing before and have the code that will do this. If you could explain fully what you want it to do with row and column details and maybe screen shots we can get a better idea of what you are looking for.
That is exactly what I need. 
Here is a screenshot. As you can see, I am trying to import from the month sheet, but I gave up on that. If it calls for VBA coding, that is way beyond me.
Here is a screenshot. As you can see, I am trying to import from the month sheet, but I gave up on that. If it calls for VBA coding, that is way beyond me.
Is the month number column in numeric value or in text value for each row.
How is this data imported or is it added manually.
If imported then having the syntax of the fields would be helpful.
If you have a excel sheet with the sample data and I can have a try with adapting my code to meet your requirements.
PM me with your email address if this is something you want me to look into writing a macro for.
How is this data imported or is it added manually.
If imported then having the syntax of the fields would be helpful.
If you have a excel sheet with the sample data and I can have a try with adapting my code to meet your requirements.
PM me with your email address if this is something you want me to look into writing a macro for.
Well, (sheepish grin) I left it in general format. The sheet tabbed main contains 12,006 lines. The monthly sheets are aprox 1000 each. Each sheet has columns and rows that total and cross-check. Pm'd ya.
I have recieved the Worksheet. The only thing I need to know to get the coding to work is what format the Mon field is in.
eg
"JAN" or "1" or "01" or "January" r "JANUARY"
eg
"JAN" or "1" or "01" or "January" r "JANUARY"
I know this is not the most amazing piece of code but it works.
There are a few things to know about it thou. 1 thing is that it will over write any previous data on the months sheet. I advice this be used as a template and data imported into the MAIN sheet. This way there will always be the correct data in each sheet.
The Month sheets are not cleared before the program runs so if there is data in there that contains more rows than the data to be entered then those rows will still have the old data in. Again another reason why the data should be cleared before running the code.
I can code this into the program if you wish.
anyway here goes.
There are a few things to know about it thou. 1 thing is that it will over write any previous data on the months sheet. I advice this be used as a template and data imported into the MAIN sheet. This way there will always be the correct data in each sheet.
The Month sheets are not cleared before the program runs so if there is data in there that contains more rows than the data to be entered then those rows will still have the old data in. Again another reason why the data should be cleared before running the code.
I can code this into the program if you wish.
anyway here goes.
CODE
Sub SplitMonth()
Dim i As Integer
Dim MonthValidate As String
Dim jani, febi, mari, apri, mayi, juni, juli, augi, sepi, octi, novi, deci As Integer
jani = 2
febi = 2
mari = 2
apri = 2
mayi = 2
juni = 2
juli = 2
augi = 2
sepi = 2
octi = 2
novi = 2
deci = 2
i = 2
Do
MonthValidate = Worksheets("MAIN").Range("B" & i).Value
If MonthValidate = "Jan" Then
Worksheets("JAN").Range("B" & jani & ":AC" & jani).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
jani = jani + 1
End If
If MonthValidate = "Feb" Then
Worksheets("FEB").Range("B" & febi & ":AC" & febi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
febi = febi + 1
End If
If MonthValidate = "Mar" Then
Worksheets("MAR").Range("B" & mari & ":AC" & mari).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
mari = mari + 1
End If
If MonthValidate = "Apr" Then
Worksheets("APR").Range("B" & apri & ":AC" & apri).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
apri = apri + 1
End If
If MonthValidate = "May" Then
Worksheets("MAY").Range("B" & mayi & ":AC" & mayi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
mayi = mayi + 1
End If
If MonthValidate = "Jun" Then
Worksheets("JUN").Range("B" & juni & ":AC" & juni).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
juni = juni + 1
End If
If MonthValidate = "Jul" Then
Worksheets("JUL").Range("B" & juli & ":AC" & juli).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
juli = juli + 1
End If
If MonthValidate = "Aug" Then
Worksheets("AUG").Range("B" & augi & ":AC" & augi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
augi = augi + 1
End If
If MonthValidate = "Sep" Then
Worksheets("SEP").Range("B" & sepi & ":AC" & sepi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
sepi = sepi + 1
End If
If MonthValidate = "Oct" Then
Worksheets("OCT").Range("B" & octi & ":AC" & octi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
octi = octi + 1
End If
If MonthValidate = "Nov" Then
Worksheets("NOV").Range("B" & novi & ":AC" & novi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
novi = novi + 1
End If
If MonthValidate = "Dec" Then
Worksheets("DEC").Range("B" & deci & ":AC" & deci).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
deci = deci + 1
End If
i = i + 1
Loop Until Worksheets("MAIN").Range("B" & i).Value = ""
MsgBox ("All data has been processed")
End Sub
Dim i As Integer
Dim MonthValidate As String
Dim jani, febi, mari, apri, mayi, juni, juli, augi, sepi, octi, novi, deci As Integer
jani = 2
febi = 2
mari = 2
apri = 2
mayi = 2
juni = 2
juli = 2
augi = 2
sepi = 2
octi = 2
novi = 2
deci = 2
i = 2
Do
MonthValidate = Worksheets("MAIN").Range("B" & i).Value
If MonthValidate = "Jan" Then
Worksheets("JAN").Range("B" & jani & ":AC" & jani).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
jani = jani + 1
End If
If MonthValidate = "Feb" Then
Worksheets("FEB").Range("B" & febi & ":AC" & febi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
febi = febi + 1
End If
If MonthValidate = "Mar" Then
Worksheets("MAR").Range("B" & mari & ":AC" & mari).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
mari = mari + 1
End If
If MonthValidate = "Apr" Then
Worksheets("APR").Range("B" & apri & ":AC" & apri).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
apri = apri + 1
End If
If MonthValidate = "May" Then
Worksheets("MAY").Range("B" & mayi & ":AC" & mayi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
mayi = mayi + 1
End If
If MonthValidate = "Jun" Then
Worksheets("JUN").Range("B" & juni & ":AC" & juni).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
juni = juni + 1
End If
If MonthValidate = "Jul" Then
Worksheets("JUL").Range("B" & juli & ":AC" & juli).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
juli = juli + 1
End If
If MonthValidate = "Aug" Then
Worksheets("AUG").Range("B" & augi & ":AC" & augi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
augi = augi + 1
End If
If MonthValidate = "Sep" Then
Worksheets("SEP").Range("B" & sepi & ":AC" & sepi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
sepi = sepi + 1
End If
If MonthValidate = "Oct" Then
Worksheets("OCT").Range("B" & octi & ":AC" & octi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
octi = octi + 1
End If
If MonthValidate = "Nov" Then
Worksheets("NOV").Range("B" & novi & ":AC" & novi).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
novi = novi + 1
End If
If MonthValidate = "Dec" Then
Worksheets("DEC").Range("B" & deci & ":AC" & deci).Value = Worksheets("MAIN").Range("B" & i & ":AC" & i).Value
deci = deci + 1
End If
i = i + 1
Loop Until Worksheets("MAIN").Range("B" & i).Value = ""
MsgBox ("All data has been processed")
End Sub
Sorry, I wasn't able to answer sooner. The month was in numbers (1 - 12), but I don't think it's all that crucial. The narrower the better for printing purposes.
If you could incorporate your code into it, that would be great. Maybe a small text file on how to use it.
As I stated in my email, we are a small family business and surely appreciate your help.
If you could incorporate your code into it, that would be great. Maybe a small text file on how to use it.
As I stated in my email, we are a small family business and surely appreciate your help.
Email is being sent to you now with the file.
There is a new sheet called Run code. This has a button that will start the code running. It will not take long to split all of the data into sheets.
Something you will need to be aware of. If the amount of columns change then the code will no longer be fully transparent.
Also please don't change the names of the sheets as this will stop the code from working also.
I hope this does the job for you. Test it and see if it is suitable.
There is a new sheet called Run code. This has a button that will start the code running. It will not take long to split all of the data into sheets.
Something you will need to be aware of. If the amount of columns change then the code will no longer be fully transparent.
Also please don't change the names of the sheets as this will stop the code from working also.
I hope this does the job for you. Test it and see if it is suitable.
Got it. Works like a charm. Is it possible to protect the month sheets to prevent inadvertent entries, or would that mess up the code working?
Each time it is run it will overright the data on the month sheet anyway and will copy direct from the MAIN sheet.
I could add in code that would protect the sheet and unprotect it to run the code then protect it again but I think this would be a bit to OTT when you could just run the code again and it would refresh from the MAIN data.
If you want anymore help on this just ask.
I could add in code that would protect the sheet and unprotect it to run the code then protect it again but I think this would be a bit to OTT when you could just run the code again and it would refresh from the MAIN data.
If you want anymore help on this just ask.
Gotcha. We'll leave well enough alone. I can't tell you how grateful we are for your time and effort. If I can ever be of service to you just shoot me an email. 
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.