VBA selecting relevant data from master sheet to populate new workbooks

Firstly many apologies to HaHoBe and John H Davis for me not replying to my original post in December. I did try them, but couldn't get them to work, must be doing something stupid.

I've been very busy since and also work then blocked access to ExcelForum.com and ended up doing them manually.

Please find attached an example of what I am trying to achieve with before and after tabs.
This example is provided in Excel 2007, but I will need it to work in Excel 2010.

The range of cells between the values in column A will vary every time, but I need to copy the rows containing the first value in column A down to the row before the next value in column A and each time copying them into cell A8 in a new workbook into and naming it as the value in column and so on until it reaches the last value in column A.

Many Thanks in anticipation.

Try this. Change the "Before" in the first line of code to match the tab name of your source data sheet.

Please Login or Register  to view this content.

Thanks for your replies guys. I've now realised that the reason why it wasn't working previously was that the values in column A were not actually values. That issue resolved now. Schoolboy error!

Using Jaslakes' approach, I've changed
With wb1
.Sheets("Sheet1").Range("A1").PasteSpecial (xlPasteAll)

to A8, as I want rows 1-7 from the original document to be copied into rows 1-7 of all of the newly created files.

I've tried with With's and End if's of wb and wb1, but it only seems to be copying over empty data. Obviously I need more training on this

Thanks in advance.

Yes still making reference to the sample file provided. Your code pretty much does what I'm after, just need finishing touches. Thanks.

Apologies for misguiding, I truncated the code to only show the beginning of that section.

This is what I have:

With wb1
.Sheets("Sheet1").Range("A8").PasteSpecial (xlPasteAll)
fName = myPath & cel.Value & ".xlsx"
Application.DisplayAlerts = False
Columns("A:A").Delete
End With
With ws
Rows("1:7").Select
Selection.Copy
End With
With wb1
.Sheets("Sheet1").Range("A1").PasteSpecial (xlPasteAll)
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
Next cel
Application.ScreenUpdating = True
End Sub

When I run this, you can tell that it has tried to copy something into rows 1-7 of the output files, but unfortunately they are empty