Splitting Worksheets from Main sheet into several sheets

Hi

First post, whilst I see similar topics (including one yesterday), none quite help me out for what I need exactly.

I have main my spreadsheet (data sheet) onto which I input the data.

I then have a separate spreadsheet which uses either =IF(Q4="","",TEXT(Q4,"mmm")) if I am converting a date into just a month (and leave blank if no value) or =IF(J9="","",J9) if I just want to copy data, (and again leave blank if nothing in this field)

In the new tab I have created (Invoice1) is where all the referenced data is.

What I need the sheet to do is look at this data, (as this is organised in the format, and columns I need for invoicing - this differs to the data input as this includes a broader spectrum of data), is to have a macro run, so it looks at the column AG (which will contain a referenced month code) - and then go down the entire spreadsheet and create tabs with the data in (from columns W to AG) in a new workbook.

The macro would need to be run at various times during the month, but not repeat information already obtained. Some of the information in column AG will be blank if this is ongoing works, and not already invoiced, so this can either be ignored or onto a separate sheet (not fussed with this)

Hello there,

Could you provide a sample of you workbook?

Thanks!

Hi there

Please find attached a copy with dummy data in.

The column highlighted in yellow is the one i need to have a macro or whatever create the tabs on.

The info in an orange/brown colour is the info I need to be copied into the relevant tabs.

Thanks in advance

Im sorry I'm unsure as to what you would like your desired results to look like. Could you possibly provide a small sample like the workbook you have above but with your desired results.

Thanks!

I have added tabs in the existing workbook, for what I would like it to do for me. Hope this makes sense!

Hello there,

Attached is your original workbook updated to include a macro that I believe accomplishes what you are trying to achieve.

To insert this code into your workbook

1.Press Alt+F8 on your keyboard
2.Clear the macro name box and type LocateCells in the blank box provided
3.Select the Create option
4.In between the Sub LocateCells() and End Sub copy and paste the code below, anything that appears in green is a comment I left to help you understand the code:

Please Login or Register  to view this content.

5.After the End Sub, copy and paste the below code:

Please Login or Register  to view this content.

6.Your entire code should look like this:

Please Login or Register  to view this content.

7.Exit out of the Visual Basic Window
8.Press Alt+F8 again and this time select the LocateCells macro
9.Select Run

Let me know if this works for you!

Thanks!

Superb! Just one question on that, the dates come up in number format, rather than date - is there a way of defaulting the columns to what they contain? Other that that THANK YOU VERY MUCH!!!

---------- Post added at 07:34 AM ---------- Previous post was at 07:14 AM ----------

It is also bringing up a debug error on the following code:

Sheets.Add(After:=Sheets(Sheets.Count)).Name = arr(i) & " " & VBA.Format(Now, "yyyy") 'add a new worksheet and set the worksheet name equal to the current item in the arr array/list and the current year

---------- Post added at 07:46 AM ---------- Previous post was at 07:34 AM ----------

Hi again

Also it seems to be carrying evrything forward to the next sheet, ie anything from June, will also appear on the July sheet

Sheets.Add(After:=Sheets(Sheets.Count)).Name = arr(i) & " " & VBA.Format(Now, "yyyy") 'add a new worksheet and set the worksheet name equal to the current item in the arr array/list and the current year

Okay to solve the second problem of wrong information on wrong tabs add the line that appears in red text in the code below to your code in the same area.

Please Login or Register  to view this content.

---------- Post added at 08:44 AM ---------- Previous post was at 08:42 AM ----------

Does this error occur when you try and run it a second time?

And does it just highlight that line of code or does it give you an error box with the error number and message? If it does can you tell me what that error code is?

---------- Post added at 08:46 AM ---------- Previous post was at 08:44 AM ----------

By your first question of changing the way the dates come up, are referring to the worksheet name?

Hi

Sorry for the delay

Comes up with Run-time error `1004`
- The name that you type does not exceed 31 characters
- Does not contain : \ / > * [ or ]
- You did not leave the cell blank

I guess this is picking up anything univoiced where there is no date on the cell

With reference to the dates, when the tabs are created for each month, the date that is copied across is coming up as a whole number rather than the date that is on the front spreadsheet (nothing to do with tab names)

---------- Post added at 02:56 PM ---------- Previous post was at 02:56 PM ----------

And the line from the debugger is :-

Sheets.Add(After:=Sheets(Sheets.Count)).Name = arr(i) & " " & VBA.Format(Now, "yyyy") 'add a new worksheet and set the worksheet name equal to the current item in the arr array/list and the current year

Are you running the code after selecting the Data Sheet worksheet?

Yes, I am always in my data sheet (invoice) when I am running the code.

Hmmm.. could you send the workbook you have saved back to me? I'm not producing the same error.

Is there a way to PM you this? I will send the full version (this includes a front page - but I have tried to remove this to see if this is affecting the problem but isnt.....)

Hi there, I have amended the data again to dummy data, and have included all pages of the worksheet including my front page. I have had to add a couple of extra columns on the invoice sheet, which will need to go into the separate tabs. I have removed the code aswell. On the front page I am asking it to work out the total costs, but have thought I can just have the tabs already set up for each month, with an autosum feature to add up the costs (this seems to work okay, and is not affected by refreshing data) - Many thanks again, sorry to slightly amend the data, but i guess thats simply changing the column references in the code, so not too much a change!!

It now needs to create/refresh tabs on column AJ, and copy data into the tabs from column X to AI

Superb, resolved!

@ surk

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.