Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Exporting And Filtering From Worksheet To Worksheet


  • Please log in to reply
13 replies to this topic

#1 PocketChange

PocketChange

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 11 January 2007 - 12:34 PM

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?

BC AdBot (Login to Remove)

 


#2 jgweed

jgweed

  • Members
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:03:45 AM

Posted 11 January 2007 - 06:56 PM

"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
Whereof one cannot speak, thereof one should be silent.

#3 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 11 January 2007 - 07:21 PM

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.

#4 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 15 January 2007 - 07:44 AM

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.
FocusToonSigStreched.jpg

#5 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 15 January 2007 - 05:32 PM

That is exactly what I need. :thumbsup:

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.

Posted Image

#6 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 16 January 2007 - 08:30 AM

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.
FocusToonSigStreched.jpg

#7 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 16 January 2007 - 09:13 AM

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.

#8 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 17 January 2007 - 05:44 AM

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"
FocusToonSigStreched.jpg

#9 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 17 January 2007 - 07:10 AM

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.

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

FocusToonSigStreched.jpg

#10 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 17 January 2007 - 07:45 AM

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.

#11 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 17 January 2007 - 08:36 AM

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.
FocusToonSigStreched.jpg

#12 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 17 January 2007 - 09:27 AM

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?

#13 projectfocus

projectfocus

  • Members
  • 479 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Brighton
  • Local time:09:45 AM

Posted 17 January 2007 - 10:09 AM

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.
FocusToonSigStreched.jpg

#14 PocketChange

PocketChange
  • Topic Starter

  • Members
  • 11 posts
  • OFFLINE
  •  
  • Local time:04:45 AM

Posted 17 January 2007 - 10:21 AM

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. :thumbsup:




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users