easy pasting problem

shattered · November 23rd, 2005, 11:45 AM

you might also want to consider sending me the data you are manipulating. The code seems to work fine on the test versions I've done so there must be something that we are missing..

reverand · November 23rd, 2005, 11:51 AM

15 12 16
14 14
16 12
14
16 12
56
45
12 16
i just made this up to test
my sheets aren't protected(at least i didn't protect them so unless it is default..) and nothing is merged(unless thats default)

shattered · November 23rd, 2005, 12:31 PM

ok, confused now, if you open a new workbook and type the above data into columns 1, 17 & 21 and then run the macro it works for me without any problem at all..

can you try the code in a new workbook and see what happens

also, what version of execl are you running ?

reverand · November 23rd, 2005, 01:17 PM

it gives me a runtime error-"Application defined or object defined error" when i press play then when i restart and press the command button it changes to the "select method of range class failed" error even when i did it in a new book. i'm running excell 2003

reverand · November 23rd, 2005, 10:46 PM

i probably should have mentioned this earlier, i don't know if it matters but i'm using visual basic for applications
The problem lies only with the range selecting, when i select a range such as "A1" it will select it and past without problems
however as you can guess that means it repastes in the same spot each time. Also, im running it off of a button. when i run it off of the run macro on the top toolbar it just gives me a msgbox"400",vbcritical

shattered · November 24th, 2005, 03:43 AM

still shouldn't make any difference, ran the code in excel 2003 without difficulty.. Have you modified the code at all - can your paste your exact code here if its not the same as above.

maccas · November 24th, 2005, 05:02 AM

Have you tried using code where you don't select any cells. Usually its not necessary to select cells / sheets in your code and will considerably speed up the macro when you cut out the selecting.

You might also consider turning Application.Calculation to xlCalculationManual while the model is running if the spredsheet is big and takes a long time toi recalculate after each paste operation.

The only other quirk with the code posted is that you cannot use the Paste method on a range object (annoyingly enough). You have to use the PasteSpecial method and then select Paste:=xlPasteAll. NB If you just want formulae / formats / values then have a look at the excel VBA help files for the other agruments that you can pass to this method.

Code:

Sub CopyData()

Dim shOrig As Worksheet
Dim shNew As Worksheet
Dim lngLoop As Long
Dim lngOutRow As Long

    ' Set up variables
    Set shOrig = ThisWorkbook.Sheets("Sheet1")
    Set shNew = ThisWorkbook.Sheets("Sheet2")
    lngOutRow = 1

    Application.ScreenUpdating = False

    ' Clear New sheet
    shNew.UsedRange.Clear

    ' Loop down Original sheet
    For lngLoop = 2 To 50000

        ' Test columns 1, 17 & 21 for criteria
        If shOrig.Cells(lngLoop, 1) > 0 And _
            shOrig.Cells(lngLoop, 17) = 0 And _
            shOrig.Cells(lngLoop, 21) = 0 Then

            ' Copy Row
            shOrig.Rows(lngLoop).Copy

            ' Paste Row
            shNew.Cells(lngOutRow, 1).PasteSpecial Paste:=xlPasteAll

            ' Clear the clipboard
            Application.CutCopyMode = False

            ' Increment Output Row counter
            lngOutRow = lngOutRow + 1

        End If

    Next lngLoop

    Application.ScreenUpdating = True

End Sub

reverand · November 25th, 2005, 01:11 AM

shattered-thanks for the help i don't know what was wrong it looked good to me, and i was using your exact code(except the next iloop)
maccas- your's worked thanks alot