Paste Method

[Raman325]

Why is the following not valid?

Selection.EntireRow.Paste

To understand the code in context, I have just copied a row using the EntireRow property and I am now pasting it into another row, except it is not working. Anyone have any suggestions?

[anilsolipuram]

can you paste the complete code

[Raman325]

Sure. The purpose of the macro is to import data from one workbook to the other. The problem is one workbook has extra lines in between the different headers (the headers and sections for each part of the Excel file have already been added and the content just needs to be put in there). Sorry if that's confusing, heres the code:

Public Sub Import()
    Static baseBook As Workbook
    Static myBook As Workbook
    Static i1 As Long
    Static i2 As Long
    
    Set baseBook = ThisWorkbook
    Set myBook = Workbooks("report.xls")

    bNumber = Application.InputBox("What is the week number?", "Week Number", Type:=1)

    i1 = myBook.ActiveSheet.Range("C1", "C" & myBook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row).Find(bNumber).Row - 2
    i2 = myBook.ActiveSheet.Rows.Count
'   basePos = baseBook.Selection.Row
    basePos = 16
    Application.ScreenUpdating = False

    For a = 1 To 7 Step 1
        baseBook.Worksheets("NCA-" & a).Activate
        Do
            myBook.Activate
            myBook.ActiveSheet.Cells(i1, "J").Select
            If Selection.Interior.ColorIndex = 2 Or (Not Selection.Interior.ColorIndex = 20 And Not Selection.Interior.ColorIndex = 37 And Not Selection.Interior.ColorIndex = 36) Then
                Selection.EntireRow.Select
                Selection.Copy
                baseBook.Activate
                baseBook.ActiveSheet.Cells(basePos, "H").Select
                Selection.EntireRow.Paste
            Else
                Set rngFindRange = baseBook.ActiveSheet.Range("H" & basePos, "BE" & baseBook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
                Set rngFoundCell = rngFindRange.Find(Selection.Value, LookIn:=xlValues, searchorder:=xlByRows)
                basePos = rngFoundCell.Row
            End If
            i1 = i1 + 1
            basePos = basePos + 1
        Loop Until i1 > i2 Or myBook.ActiveSheet.Cells(i1, "J").Value = "BREAKFAST"
    Next a
    Application.ScreenUpdating = True
End Sub

Thanks again for your help.

[anilsolipuram]

baseBook.ActiveSheet.Cells(basePos, "H").Select
Rows(Selection.Row & ":" & Selection.Row).Select
ActiveSheet.Paste

[K Dales]

Hard to be sure without seeing the code, but I suspect you are pasting into a
cell that is not in column A. If you are doing this it will fail since the
"entire row" contains cells (blank ones count!) that would have to be pasted
beyond the upper limit of the worksheet columns. This happens if you do it
manually, too (select a row, copy, and paste into a cell in B and you will
see).

If this is indeed the reason it fails, paste into the destination cell's
..EntireRow and it should work.

"Raman325" wrote:

>
> Why is the following not valid?
>
>
> Code:
> --------------------
> Selection.EntireRow.Paste
> --------------------
>
>
> To understand the code in context, I have just copied a row using the
> EntireRow property and I am now pasting it into another row, except it
> is not working. Anyone have any suggestions?
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=383224
>
>

[jjk]

The EntireRow property should work even if the Selection column is not
A.

I can think of one possibility where the EnitreRow can fail.
If the the row contains merged cells is some format. This would mean
that the paste action cannot change a part of the destination merged
cells.

However, it is tough to tell why without looking at the code and the
sheet layout.

Regards,
Jayant

[Raman325]

That fixed it Anil, thank you very much. I also appreciate the comments and suggestions from jjk and K Dales.