VBA to copy selected cells down to empty cell and paste

**rioutousgnome** · 01-28-2013, 11:35 AM

Hi all

Im a new member and struggling to self teach myself VBA which i must admit i have caught the bug. but I have reached my limit

problem

I have a spreadsheet with schools names and pupils underneath, no gaps.

ie

A_____________ B ___________ C______________ D

1 St peters
2 gary___________smith _________123___________122
3 st michael
4 gary __________smith__________123___________123

i need the st peters pupils moved into a new spreadsheet called st peters with pupils underneath moved to that sheet, then the next school and so on.

currently i am highlighting the school name and dragging down till i reach next school, the only difference in the lines is next to the school is an empty cell in column B as above.

I am using this script so far which i pieced together from other posts.

Sub AddSheet()
Range("A1:E65536" & Cells(65536, 8).End(xlUp).Row).Copy
Dim strName As String
strName = Sheets(1).Range("A1")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
ActiveSheet.Paste
ActiveWorkbook.Sheets(1).Activate
Selection.Delete Shift:=xlUp
End Sub

This works fine with some manual imput but i want to automate the highlighting the schools and moving them manually till sheet 1 is empty of data.

I need to substitute the manual process of selecting the cells by having a script that will highlight from A1 all rows down till colum B is empty, then opening a new sheet and using A1 as the sheet name and moving the highlighted cells over as my script currently does.

I would be extermely grateful if someon can help me with the first part of the script.. I'm stuck..

Thanks in advance.

**stnkynts** · 01-28-2013, 11:50 AM

Hello rioutousgnome,

I would be happy to assist you but I need some clarification first. Your spreadsheet layout is unclear to me. Could you either submit a screenshot or a test workbook with a before code and after code example. In other words,an example showing what you would like it to look like before the script runs, and what you would like it to look like after the script.

Just a side note, almost all VBA is project specific and unless you are versed in it, it is really difficult to piece parts together into something usefull.

**rioutousgnome** · 01-28-2013, 12:55 PM

hi and thanks

i have attached a screen print, obviously the data is fake as its confidential information but gives u an idea.

the sheet has 4 school names and under each name will be pupil data. the school name ends in column A so column B is blank next to the school name.

I need each school to have its data transferred to a new sheet with the sheet named by the school name.

so in this instance i would end up with 4 sheets named st peters, st james, st joseph and st michaels. each sheet will have the A1 cell with name and then underneath the data.

does this make sense?

**rioutousgnome** · 01-28-2013, 12:57 PM

hi and thanks

i have attached a screen print, obviously the data is fake as its confidential information but gives u an idea.

the sheet has 4 school names and under each name will be pupil data. the school name ends in column A so column B is blank next to the school name.

I need each school to have its data transferred to a new sheet with the sheet named by the school name.

so in this instance i would end up with 4 sheets named st peters, st james, st joseph and st michaels. each sheet will have the A1 cell with name and then underneath the data.

does this make sense?

**stnkynts** · 01-28-2013, 01:36 PM

Thanks for the pics. Try this:

Please Login or Register  to view this content.

**rioutousgnome** · 01-28-2013, 01:42 PM

OMG you are amazing that worked, i would never have got that, i cant even understand most of that but it works, thanks.

any tips on the best way to learn VBA?

thanks again

**stnkynts** · 01-28-2013, 01:55 PM

start with VBA for Dummies. There are also a lot of threads on this site where people list what they think are the best ways to learn VBA. Also, if you are satisfied with my assistance I would be more than happy to receive a reputation bump from you. You can do this by clicking the star under my name.