Wont copy and paste using VBA

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.

0
tim_stone (1)
2/20/2007 12:00:25 PM
excel 39879 articles. 2 followers. Follow

3 Replies
374 Views

Similar Articles

[PageSpeed] 48

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.

tim_stone@jltgroup.com wrote:
> 
> 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.

-- 

Dave Peterson
0
petersod (12004)
2/20/2007 1:10:30 PM
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
dguillett1@austin.rr.com
<tim_stone@jltgroup.com> wrote in message 
news:1171972821.407247.212930@q2g2000cwa.googlegroups.com...
>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.
> 


0
dguillett1 (2487)
2/20/2007 1:45:29 PM
On Feb 20, 1:45 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> 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
> dguille...@austin.rr.com<tim_st...@jltgroup.com> wrote in message
>
> news:1171972821.407247.212930@q2g2000cwa.googlegroups.com...
>
>
>
> >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.- Hide quoted text -
>
> - Show quoted text -

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.

0
2/20/2007 6:49:24 PM
Reply:

Similar Artilces:

Cannot log in using WindowsID (was able to before)
I just bought MS Money 2007 Deluxe yesterday. The install went smooth, without any issues. I registered a new WindowsID and was able to successfully log into MS Money. However, today when I tried to log into MS Money, the messge apeared that the "Money was not able to recognize your WindowsID" or something like it and that "You will still have access to some functions but will not be able to make online updates". Now I'm not able to make updates. Any idea what this could be? Is the authentication server for WindowsID down or something? See the post under Mon...

Can't use Merge feature
If I have a postcard file I've already made on the screen, when I try to pull up the "Merge" feature under Tools, it is shaded grey and I can't open it. It insists that I either form a new database list of addresses or edit the one that I want to merge. I don't want to edit the one I want to merge. I just want to MERGE it. If, however, I have just a postcard template on the screen (without it having been made into anything), I CAN pull up the "merge" feature under Tools. It is not shaded out. How do I un-shade the "merge" feature? To merg...

I'm missing all past years emails in my hotmail account
For some reason i logged in into my hotmail account and noticed that all my past years are gone. I havent deleted anything. I'm missing emails from 2006 till 2009 all of them? Please help....what can i do? If the Hotmail account is set as your primary store, could it be that you have AutoArchive enabled? Tools-> Options-> tab Other-> button AutoArchive... -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Re...

Restore Exchange 5.5 on 2000 using ArcServe
I've got - Windows NT 4 Server SP6 - Exchange 5.5 SP3 - ArcServeIT 6.61 with Exchange Agent I'm trying to restore the Exchange information (database, mailbox... everything) on another computer. That computer has - Windows 2000 Server SP4 - Exchange 2000 - Brighstore ArcServe 9 (build 2020) with Exchange Agent ------------------- Now the situation: I need to verify that the backup was made correctly (entirely) in order to confirm the total validity of the backup (to validate my disaster recovery plan) and to update to SP4. I tried to find documentation with both Microsoft (for Ex...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

copy of exchange 2000
I don't suppose microsoft is providing Exchange 2000 as a free download anywhere are they?? Until we can afford to move up to SBS 2008 (hopefully later this year), I want to trash our SBS 2000 (too many issues with it) and simply install a win2k server with Exchange until we can move on. We are not using any of the other SBS components any longer anyway... If anyone knows where I might be able to get this from, I would be very appreciative! Thanks, Brad Why not look at BPOS? $10 per user a month it's a great inexpensive solution. You may find you do not ne...

Printing Multiple Copies #4
Hi all - I am using Vista Business and Publisher 2007 from the Office 2007 Small Business package. No matter what type of publication or template I use I can only print single copies. Most recently I tried to print 30 copies of a single page flyer and had to do it a single copy at a time. If I go to File|Print and set the number of copies to 30, I still get a single copy only. Does anyone have any thoughts? Thanks -- Andrew Aitchison (amhsn@nospam.sympatico.ca) Is your printer driver current? What is the default in the printing preferences in the control panel printer folder? ...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Using Excel in Mac office 2004 with Microsoft/Mac 2008:Analysis toolpack
Although I am disappointed with Office 2008 for Mac because of the absent of Analysis toolpack, I figured things out quite recently that I can still install my Mac office 2004 and use the excel package that contains the Analysis tool pack. How? ...... let me show you: 1. Insert your CD for Mac office 2004 2. When it open on your desk top, two options will be given to you to download the software (a) Office set-up assistant, and (b) Microsoft office 2004. 3. CHOOSE MICROSOFT OFFICE 2004 (Drag the icon onto your desk top) and it will start to run/download. 4. When done, this will appear as ...

How to obtain enumerated attribute declarations from a DTD using MSXML & VB?
I have a VB (VB6, not .NET) application that reads an XML file (using MSXML v3.2 parser); the XML file contains a reference to an external DTD. The DTD has numerous enumerated attribute declarations like so: <!ATTLIST MyElement MyAttribute (One | Two | Three) "One" > The VB code needs to generate dynamic pick-list for all the attributes that appear in the DTD as part of a data-entry application; each dynamically created pick list should contain all the allowable values for the attribute as defined by the DTD (and presented in some kind of multiple choice control like a comb...

Using Excel spreadsheet as input to Access
Hello, I posted this in the New Users forum but only got one answer, so thought I'd try here as well. Like so many others, I am an Excel newbie. I was a mainframe COBO programmer in another life, but that was a few years back My manager would like me to write an app that will take tracking dat from an existing Excel spreadsheet (generated by our system) but onl use a select handful of columns as input to a new Access database tha I will create. I'm guessing that I can either a) create a new edited spreadsheet to b used as input to the Access database or b) use the Import wiza...

MS Excel Paste Special>Paste All
Using MS Excel 2003 Paste Special> Paste All, doesnt quite paste the 'Column Widths'. Am I always required to do the two step dance, Paste Special> Column Widths and Paste Special>All. >>Am I always required to do the two step dance Yes<g>. -- Jim "Excel User" <Excel User@discussions.microsoft.com> wrote in message news:5D34ED9D-5BD6-43AD-8773-1EB7229309B2@microsoft.com... | Using MS Excel 2003 | | Paste Special> Paste All, doesnt quite paste the 'Column Widths'. Am I | always required to do the two step dance, Paste Special> ...

keep past calendar appts longer than six weeks
Outlook is deleting past calendar appointments after approx six weeks. How do I change setting to keep for a longer period of time? thx. Check your archive settings. You didn't indicate your version of Outlook so the instructions are approximate: Tools > Options > Other > Auto Archive. -- Kathleen Orland "tfg" <tfg@discussions.microsoft.com> wrote in message news:FA512107-117A-477D-9786-DDED3BC9D94E@microsoft.com... > Outlook is deleting past calendar appointments after approx six weeks. How > do I change setting to keep for a long...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

Using Risk+ with MSP 2007
We use Risk+ as a risk simulation tool. We have discovered that it works approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run into this problem. If so, is there a remedy? My hunch this not a generic issue, but surely best that you consult with the Risk+ people on this. --rms www.rmschneider.com On 02/03/10 18:08, Tom Mc wrote: > We use Risk+ as a risk simulation tool. We have discovered that it works > approximately 20 times SLOWER in MSP 2007 than MSP 2003. Has anyone else run > into this problem. If so, is there a remedy? W...

Printing Word and excel documents via excel vba
I have a document that has some Excel and some word content i would like to use excel vba to print both a page from word and then a page from excel. I know how to select and open the word document, however i cant find any code to print page X from word. basically i need to: print page 1 of c:\xx\document1.doc print page1 of sheet1 of c:\yy\excelwkbk1.xls print page 2 of c:\xx\document1.doc print page2 of sheet1 of c:\yy\excelwkbk1.xls and so on it will require moving between word and excel - the excel bit i get, but getting back to excel from word, and printing out in word is slightly more...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

how to use roundup
how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 to 1251000 , 1250750 to 1250800 thank alot =ROUNDUP(A1,-2) -- Kind regards, Niek Otten "r4319hb" <r4319hb@discussions.microsoft.com> wrote in message news:A8D5EA24-858C-49D3-A5ED-481FEBEA36CB@microsoft.com... > how to roundup the number to hundreds example 1250101 to 1250200 , 1250901 > to > 1251000 , 1250750 to 1250800 > thank alot =CEILING(A1,100) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "r4319hb" <r4319hb@discussions.microsoft...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

Using 'like' on a user prompt
I want to prompt the user for a value to use to filter a form. Here is my command with the WhereCondition: docmd.OpenForm "test", acNormal, , "LastName=[Enter a Last Name]" I want to use the WhereCondition as a "LIKE" criteria. Variations on the WhereCondition itself that I've tried include various combinations of LastName LIKE %...% LastName LIKE *...* LastName LIKE [%...%] LastName LIKE [*...*] You get the idea... No luck so far. Any thoughts? On Thu, 27 May 2010 21:52:50 GMT, "RicDon" <u60397@uwe> wrote: >I wan...

Using online services from Europe
I'm going to be temporarily stationed in Europe for a couple of months. Are there any problems with my using M2002 to access my accounts, pay bills, etc, from there just as I do from the US? TIA You should be fine, so long as you have a 128 bit encryption version of IE. If you do have problems, please feel free to post here :-) -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh...

How to add series with VBA?
How can I add multiple series to an existing Excel graph using automation from Access 2000? I'm trying to set up a loop that will define the Xvalue and YValue for each series, but not sure how. I'm creating up to 100 Excel worksheets at a shot with data from Access 2000, and each worksheet needs to have a graph with multiple series. The worksheet creation loop looks like this: For i = 1 to sn.Count db.Execute "SELECT * INTO [Excel 8.0;Database=" & strXlsPath & "].[" & _ strSheetName & "] FROM tblExcelData", dbFailOnError Next Then ...

Can I use the same driver in all versions of Access or Sqlserver
Hello,I have got a question,I wonder if i can use the same driver as Access2000 to get the data from all versions of Access database,my code connected to Access2000 is as follows: "m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+s.m_database,"","",adModeUnknown); Is this code available for other versions of Access? If not,can anyone tell me the correct code for Access2002.Access2003,Access97? my code connected to Sqlserver2000 is as follow: m_pConnection->Open(Provider=SQLOLEDB.1;Persist Security Info=True;Password="+s.m_pass...