VBA to split worksheet into multiple sheets. Based on rows. Keeping formatting and headers

[Aperras7]

Hello all,

I know that this question has been asked a thousand time before, but I can't find the perfect one for me. I'm looking for a VBA that takes all my rows and separate them into multiple sheets in the same document. Here's exactly what i'm looking for:

- something that keeps my formating and cell width.
- something that would give the name of the first ell in each row to the new sheets (every row begins by a unique code)
- something that will copy the headers for every new sheets

As I said, every row begins by a different code. The table is called Leases and here's the data reference including headers : A1:AY201

I'm using Excel 2007 by the way.

thank you all,



Example :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store No.[/TD]
[TD]Location[/TD]
[TD]Fran/Corp/Sub[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]P*** - Street Level[/TD]
[TD]Corp[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]3.1[/TD]
[TD]P*** - Mezzanine Level[/TD]
[TD]Corp[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Take a look at this very elegant solution : DataPig Technologies Dashboard Tools

HTH

 

[Aperras7]

That website doesn't work for me. Company is blocking it. Can't you post me the VBA code?

Also, I can't download anything to help me do it.

Thanks,

 

[James006]

Shame you cannot access this site from work ...
You could try to access it from elsewhere ...
If this project is important enough for you, you can construct your own program.

Let me recap the principles you need to apply to build the solution to your problem :
1. Test and learn the advanced filter capabilities
2. Turn on the macro recorder to visualise the VBA code
3. Improve on this basic code to reach your final solution

HTH

 

[Aperras7]

Considering my VBA knowledge, it wouldn't help me much to only have something that i'll have to modify and to develop myself.

Isn't there anything already existing?

Thanks,

 

[Kellens]

I have some VBA code that works perfect for the ROWS but not the columns, maybe easy to adjust this?

Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & "choose-name" & ".xlsx"
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

Hope this helps you further...