Wont copy and paste using VBA

T

tim_stone

I have a very large worksheet with a number of people on it. There are
multiple rows of data for each of these people.

I have managed to write code to set up a member tab for each of these
people (over 1000 !) but what I now want to do is copy the data for
each person to the relevant worksheet.

This is my code:

Sub ExportData()
Dim wb As Workbook
Dim ws As Worksheet
Dim strSheetName As String

Set wb = Workbooks("PWSallocationsv1 3 (2).xls")

For Each ws In wb.Worksheets

If Not ws.Name = "Sheet1" Then
If Not ws.Name = "Raw Data" Then
'MsgBox ws.Name
strSheetName = ws.Name

Sheets("Raw Data").Select
Selection.AutoFilter Field:=3, Criteria1:=strSheetName
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets(strSheetName).Select
Range("A1").Select
ActiveSheet.Paste
End If
End If

Next ws

End Sub

However, I have two issues here. Sometimes when I run it I get a
message saying that 'Excel can not complete this task with available
resources. Choose less data or close other applications'. This is not
a memory issue as this is being run on a Citrix server.

The other is that the code copies the first person's data but then
loops thorugh the rest without putting the data in the sheet. The
sheets are being selected and if I step through the code, the data is
being copied to the clipboard but it not pasted.

Can anybody help with either of these two issues.
 
D

Dave Peterson

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

=======
I don't have a guess why the procedure fails with the memory error, but the
Selection stuff is causing the copy|paste to fail.

I dropped the Selections and this worked ok for me under light testing:

Option Explicit
Sub ExportData()
Dim wb As Workbook
Dim ws As Worksheet
Dim RDWks As Worksheet

Set wb = ThisWorkbook 'Workbooks("PWSallocationsv1 3 (2).xls")

Set RDWks = wb.Worksheets("raw data")

For Each ws In wb.Worksheets
Select Case LCase(ws.Name)
Case Is = "sheet1", "raw data"
'do nothing
Case Else
'remove the arrows
RDWks.AutoFilterMode = False

'reapply the filter
RDWks.UsedRange.AutoFilter field:=3, Criteria1:=ws.Name

RDWks.Cells.Copy _
Destination:=ws.Range("a1")
End Select
Next ws

'remove the filter
RDWks.AutoFilterMode = False

End Sub

I did remove the autofilter and reapply it. I assumed the headers were in the
top row of the used range (row 1 in my test data).

And in some cases, Select Case is easier to read. So I used that, too.
 
D

Don Guillett

UN tested but try

Sub ExportData()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("PWSallocationsv1 3 (2).xls")

For Each ws In wb.Worksheets
if ws.Name <> "Sheet1" and ws.Name <> "Raw Data" Then
with Sheets("Raw Data").AutoFilter Field:=3, Criteria1:=ws.name
.SpecialCells(xlCellTypeVisible).Copy ws.Range("A1")
end with
end If
Next ws
End Sub
 
T

tim.stone

UN tested but try

Sub ExportData()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("PWSallocationsv1 3 (2).xls")

For Each ws In wb.Worksheets
if ws.Name <> "Sheet1" and ws.Name <> "Raw Data" Then
with Sheets("Raw Data").AutoFilter Field:=3, Criteria1:=ws.name
.SpecialCells(xlCellTypeVisible).Copy ws.Range("A1")
end with
end If
Next ws
End Sub

--
Don Guillett
SalesAid Software

















- Show quoted text -
Click to expand...

Thanks - the code works but I am getting the dreaded 'not enough
resources' message. This is aparanetly to do with Excel's own memory
management rather than the servers memory.

However, one this that I notice is that if I go to the 'Raw Data'
sheet then the last cell used is S358 but if I go to one of the other
sheets ater the data has been copied then the last cell used is S64247
so I think this may have something to do with the memory usage.

Any ideas why the area copied would be so much bigger at the original
data.
 
Top