Results 1 to 36 of 36

error message when Automating copy/paste worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    error message when Automating copy/paste worksheets

    Hello all,
    I have been working on this macro all day and no matter how many different ways I write it I can't seem to get it to finish correctly without giving me an error message. The error messages are different depending on how I write the macro, so what the message says is not important.

    I have attached a sample of my work which contains the macro, but I'll post the code below as well. There are two tabs in the workbook. One contains the data("download") and the other is the template("Blank") per say. I need the macro to create a new worksheet using the template for each line of the data ("download")until it gets to the "finalrow." It names the worksheets the value of D3 currently, but I'd like to figure out how to name the worksheets by C3. c3 contains the names of customers, and is duplicated for each product they have purchased. I'd just like to attach a number after the name if it is a duplicate. i.e. Adam, Adam1, Adam2, Adam3 for all "Adam"s.

    Look into the code and see if that makes sense. if not, feel free to ask questions. I appreciate the time you take to help me resolve this issue!

    thanks

    Sub automate_linesheets()
    '
    ' automate_linesheets Macro
    ' Macro recorded 10/8/2009 by Kelly.Householder
    '
    
    Dim finalrow As Long
    Dim finalcolumn As Long
    
        Sheets("Download").Select
        ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
        finalrow = ActiveCell.Row
        Debug.Print finalrow
        finalcolumn = ActiveCell.Column
        Debug.Print finalcolumn
    
        Sheets("Download").Select
        Range("C3").Select
        Range(Cells(3, 1), Cells(finalrow, finalcolumn)).Sort Key1:=Range("C3"), Order1:=xlAscending, Key2:= _
            Range("D3"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    
    'Range(Cells(3, 1), Cells(finalrow, 30))
    Dim I As Long
        
        For I = 3 To finalrow
        Sheets("Download").Select
        Cells(I, 2).Select
            If Cells(I, 11).Value >= 0 And Cells(I, 12).Value >= 0 Then
               Range(Cells(I, 1), Cells(I, 34)).Select
               'Rows(I).EntireRow.Select
               Selection.Copy
               Sheets("Blank").Select
               Range("A3").Select
               ActiveSheet.Paste
               Sheets("Blank").Select
               Sheets("Blank").Copy After:=Sheets(2)
               ActiveSheet.Name = Range("D3")
               'On Error Resume Next
            End If
         
         Next I
    
    End Sub
    Attached Files Attached Files
    Last edited by curbster; 10-09-2009 at 08:03 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1