Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PasteSpecial (Excel 2000)

    I enclosed a macro saved as a Word document. I can't understand the error message #1004 "Copy area and Paste area are not the same size and shape" Line 30.
    The worksheet has a column of names. When the names change I want to copy the entire last row of each group.
    .
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (Excel 2000)

    You're copying an entire row, then trying to paste it into cell H1. You can only paste an entire row to an entire row or to a cell in the leftmost column, column A. Trying to paste an entire row anywhere else will fail. But it's not clear to me where you actually want to paste the line. If you keep on pasting to the same cell, you'll overwrite the previous paste each time.

    Notes:
    - You should use xlPasteValues instead of xlValue in the PasteSpecial line.
    - You loop through the cells of the selection, but keep on changing the selection inside the loop, this makes the result unpredictable.

    If you explain more clearly what you want to accomplish, someone may come up with a suggestion.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hello Hans, the attatchment is the macro. It looks like only one attatchment can be forwarded.
    What I'm trying to do is copy the last row of each group to another sheet. The macro hangs up on the last line of the first group and I can't seem to move to the next group and do it over again until all the last lines are copied.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    This is the worksheet
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (Excel 2000)

    Try this version.
    <code>
    Sub CountandArrays()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim iRowS As Integer
    Dim r As Integer
    Dim I As Integer

    Set wshSource = Worksheets("Sheet1")
    Set wshTarget = Worksheets("Sheet2")

    ' Count rows in source sheet
    iRowS = wshSource.Cells(wshSource.Rows.Count, 4).End(xlUp).Row
    For r = 1 To iRowS
    Application.StatusBar = "Processing Row No." & r
    If wshSource.Cells(r + 1, 4) <> wshSource.Cells(r, 4) Then
    I = I + 1
    wshSource.Cells(r, 4).EntireRow.Copy
    wshTarget.Cells(I, 1).PasteSpecial Paste:=xlPasteValues
    End If
    Next r
    End Sub
    </code>
    This version contains only one loop, and it doesn't select cells explicitly.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hans, It works. Thanks again. You're a great teacher

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hans, that macro worked fine. I wanted to expand. Opened another workbook and tried to append to the first blank row using CurrentRegion. Keep getting error message 1004 Copy area and Paste area not the same
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (Excel 2000)

    wshTarget.Range("A1").CurrentRegion is not the first blank row, it's the largest rectangular region containing A1 that doesn't contain blank rows or columns.

    If you know for sure that column A will be populated, use

    wshTarget.Range("A" & wshTarget.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hans, the macro works just fine. However I would like to type in the name of the workbook only once.How can I substitue a variable instead of the actual wookbook name? --wshSource(Workbook.Aqu040409_hr.xls etc.
    see attatchment.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (Excel 2000)

    It's not clear what you're doing, but in your previous attachment you had a line

    Set wshSource = Workbooks("sa_080229_hr").Worksheets("RECAP")

    You could change this to something like the following:

    Const strWorkbook = "sa_080229_hr"

    and use strWorkbook wherever you need to refer to the workbook name, e.g.

    Set wshSource = Workbooks(strWorkbook).Worksheets("RECAP")

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hans, The macro begins with SelectRace=Application.InputBox(Prompt-----------
    Would the next line be:Const strWorkbook="SelectRace" ????
    In other words can I use SelectRace instead of the actual workbook name?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (Excel 2000)

    Your macro uses the SelectRace as the sheet name. Is it also to be used as the workbook name? If so:
    - If the workbook is already open:

    Dim wbk As Workbook
    Set wbk = Workbooks(SelectRace)

    - If the workbook hasn't been opened yet:

    Dim wbk As Workbook
    Set wbk = Workbooks.Open(SelectRace)

    You can then use

    Set wshSource = wbk.Worksheets(...)

    But it might be better to use the standard File Open dialog to let the user open a workbook, e.g.

    Application.Dialogs(xlDialogOpen).Show

    or look up the help for GetOpenFilename.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Hans, I'm not making any headway. The sheet name and the workbook name are the same. When I run the macro the input box prompts me to enter the filename. I enter the filename and the Immediate window indicates that SelectRace equals the name I entered. However the procedure crashes on the line--- Sheets("SelectRace").Select (attatchment)
    Attached Files Attached Files

  14. #14
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PasteSpecial (Excel 2000)

    Does this mean that you can't use a string variable to refer to a sheet name?

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PasteSpecial (Excel 2000)

    The line:
    Sheets("SelectRace").Select

    Will try to select a sheet called "SelectRace" in the activeworkbook. Does the activeworkbook have a sheet with this literal name?

    I think you want the variable callec SelectRace, not a string:
    Sheets(SelectRace).Select

    Also you do not specify the workbook, so the activeworkbook is implicitly assumed...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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