none
Problem to paste RRS feed

  • Question

  • Hi,
    I get

    due to last line below
    Sub Retrieve_Range(Para_Range As String)
        Dim RowID As Integer, Count0 As Integer, Start_Row As Integer, End_Row As Integer
        
        With Worksheets("Utilization-PO-List").Sort
            With .SortFields
                .Clear
                .Add Key:=Range("C1:C1") _
                        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End With
            .SetRange Range("A:S")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
        RowID = 2
        Count0 = 0: Start_Row = 0: End_Row = 0
        
        Do While True
            If Trim(Worksheets("Utilization-PO-List").Cells(RowID, 2).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID, 4).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID, 5).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID, 6).Value) = "" Then
                Exit Do
            End If
            
            If Format(Worksheets("Utilization-PO-List").Cells(RowID, 3).Value, "yyyy-MM") = Format(Worksheets("Budget-Report-HO").Cells(5, 5).Value, "yyyy-MM") Then
                If Start_Row = 0 Then
                    Start_Row = RowID
                End If
            End If
            
            If Start_Row > 0 Then
                If Format(Worksheets("Utilization-PO-List").Cells(RowID, 3).Value, "yyyy-MM") <> Format(Worksheets("Budget-Report-HO").Cells(5, 5).Value, "yyyy-MM") Then
                    If End_Row = 0 Then
                        End_Row = RowID - 1
                        Para_Range = "A" & Trim(CStr(Start_Row)) & ":S" & Trim(CStr(End_Row))
                        Exit Sub
                    End If
                End If
            
            End If
            
            RowID = RowID + 1
        Loop
        
        If End_Row = 0 Then
            End_Row = RowID - 1
        End If
        
        If Start_Row > 0 And End_Row > 0 Then
            Para_Range = "A" & Trim(CStr(Start_Row)) & ":S" & Trim(CStr(End_Row))
        End If
    End Sub
    ...
                Dim Range0 As String
                Retrieve_Range Range0
                
                If Range0 <> "" Then
                    Windows(Current_Book).Activate
                    Worksheets("Utilization-PO-List").Range(Range0).Copy
                    
                    Windows(Window0).Activate
                    Dim RowID1 As Integer
                    RowID1 = 2
                    Do While True
                        If Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 2).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 4).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 5).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 6).Value) = "" Then
                            Exit Do
                        End If
                        
                        RowID1 = RowID1 + 1
                    Loop
                    
                    If RowID1 > 1 Then
                        'Dim B0 As String
                        'B0 = "A" & Trim(CStr(RowID1)) & ":" & "S" & Trim(CStr(RowID1))
                        Worksheets("Utilization-PO-List").Cells(RowID1, 1).Select
                        'Worksheets("Utilization-PO-List").Range(B0).Copy
                        Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
                        Worksheets("Utilization-PO-List").Range("A" & Trim(CStr(RowID1))).PasteSpecial
    					...
    


    why?

    Many Thanks & Best Regards, Hua Min

    Wednesday, May 18, 2016 5:22 AM

Answers

  • Hi HuaMin  Chen,

    Did you try to using only paste instead of PasteSpecial?

    you can try to test whether it can work.

    The code below End Sub is like part of another sub.

    and I think the issue is only related with it and there is no issue with the sub "Retrieve_Range" because all the issue is occur after that.

    did you try to check that the line below you have used to copy have values copied.

    Worksheets("Utilization-PO-List").Range(Range0).Copy
    

    please confirm this first. Range0 having values and copied.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jackson_1990 Thursday, May 19, 2016 4:16 AM
    Thursday, May 19, 2016 3:02 AM
    Moderator

All replies

  • You have the following line to copy the data commented out so nothing to paste.

    'Worksheets("Utilization-PO-List").Range(B0).Copy

    If that is not the problem then when the code stops select Debug and then hover the cursor over the variable RowID1 and ensure that it is valid to create a valid reference.

    Also why are you using PasteSpecial with no parameters. That is the same as simply Paste.

    If you unprotect the worksheet first then you can copy and paste like the following. (Does not work with PasteSpecial)

    Note that the space and underscore at the end of a line is a line break in an otherwise single line of code.


    Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
    Worksheets("Utilization-PO-List").Range(B0).Copy _
        Destination:=Worksheets("Utilization-PO-List").Range("A" & Trim(CStr(RowID1)))


    Regards, OssieMac

    Wednesday, May 18, 2016 7:29 AM
  • But I have such copy line

    Worksheets("Utilization-PO-List").Range(Range0).Copy

    in above and how to ensure it is fine to copy from one Workbook to another?


    Many Thanks & Best Regards, Hua Min

    Wednesday, May 18, 2016 7:50 AM
  • Any other help?

    Many Thanks & Best Regards, Hua Min

    Thursday, May 19, 2016 1:19 AM
  • Hi HuaMin  Chen,

    Did you try to using only paste instead of PasteSpecial?

    you can try to test whether it can work.

    The code below End Sub is like part of another sub.

    and I think the issue is only related with it and there is no issue with the sub "Retrieve_Range" because all the issue is occur after that.

    did you try to check that the line below you have used to copy have values copied.

    Worksheets("Utilization-PO-List").Range(Range0).Copy
    

    please confirm this first. Range0 having values and copied.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Jackson_1990 Thursday, May 19, 2016 4:16 AM
    Thursday, May 19, 2016 3:02 AM
    Moderator