Copy and Paste information from woorksheet onto different workbook

[Guest]

Hi, I currently have about 10,000 rows of data and need to filter and copy
and past certain amounts of data onto different workbooks. I need to filter
by column A and copy and paste all the filtered numbers onto a different
workbook. In total there will be about 60 different workbooks each
containing a different number of rows. I need to save the workbooks as
something different everytime I I dont know how to go about doing this....can
anyone help?

 

[Bernie Deitrick]

T-Bone,

You could use a macro - the one below does exactly what you want. Select a single cell in your data
table, then when asked

What column # within database to use as key?

Answer 1 (your column A).

This may have problems if you are using numbers instead of text in column A, but give it a try....

HTH,
Bernie
MS Excel MVP

Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name = myShtName Then
Exit Sub
Else
mySht.Move
ActiveWorkbook.SaveAs "Workbook " & ActiveSheet.Name & ".xls"
ActiveWorkbook.Close
End If
Next mySht
End Sub

 

[Guest]

Hi Bernie,

Thnak you so much for this, however, I have encountered problems when typing
1 in the answer. Let me elaborate a bit more about what I need to do.

In my Column A, I have 4 unique numbers (all 6 digits long) - this is a test
sheet, the real sheet has over 60 numbers (10,000+ rows) - hence my need for
a macro rather than cutting and pasting.

Each unique number has different number of rows populated (for e.g Unique ID
109382 has 20 rows and unique ID 134221 has 65 rows) Each unique ID and
thier rows (columns A,B,C,D&E) have to be placed on a seperate workbook each.


I have tried recording a macro (I dont know VBA) and filtering column A,
cutting out the first unique number (and 20 rows) and pasting onto a new
workbook and saving. Then I will stop recording, run the macro, and when I
save, it want's to overwrite my previous save....to which I dont want it to
do. Is there any chance I can give you a copy of my spreadsheet to show you
what I am trying to do?

Thank you

T-bone

 

[Bernie Deitrick]

Sure, send it to me - take out the space, change the at to @ and the dot to .

HTH,
Bernie
MS Excel MVP