+ Reply to Thread
Results 1 to 5 of 5

pastespecial to first blank row in range

Hybrid View

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    pastespecial to first blank row in range

    My current code works great for "column A" (code attached), but not so great for "column Y" (code also attached). The error is for run time error 1004 (copy and paste not same size). Column A will be empty for this code so it's not an option. Also there is other data, not related above and below my range of 3000-3111 so......... I can't seem to get it to work properly. Any help is greatly appreciated.
    this one works
    Sub IA_Company_2()
    
    Dim b As Range, FoundCell As Range, LastCell As Range, lastrow As Range
    Dim ws1 As Worksheet
    Dim FirstAddr As String
    Dim lCount As Long
    
    Set ws1 = ThisWorkbook.ActiveSheet
    Set ws2 = ThisWorkbook.Sheets("Contacts") ' CHANGE THIS TO THE NEW IA COMPANY LOGBOOK
    Set b = ws2.Range("a8:e9999")
    
    ws1.Unprotect "master32"
    
    Application.ScreenUpdating = False
    
    a = InputBox("What is the IA Company's Name you are adding?     HINT: You can type RHI to look for Rhino Claims.", "IA Company", Rhi)
    If a = "" Or a = False Then
        GoTo 100
    End If
    With b
        Set LastCell = .Cells(.Cells.Count)
    End With
    lCount = 1
    Set FoundCell = b.Find(what:=a, LookAt:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Copy
        ws1.Range("a3000:a3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
        Set FoundCell = b.FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
        lCount = lCount + 1
    Loop
    If lCount > 30 Then
        MsgBox ("Too many matches......Try narrowing your search.  Be more specific")
        GoTo 100
    End If
    ws1.Range("ab2998") = "IACOMP2"
    100
    ws1.Protect "master32"
    Application.ScreenUpdating = True
    ws1.Range("a3013").Select
    This one doesn't
    Sub IA_Name_1()
    
    ActiveSheet.Unprotect "master32"
    Dim b As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim FoundCell As Range
    Dim LastCell As Range
    Dim FirstAddr As String
    
    Set ws1 = ThisWorkbook.ActiveSheet
    Set ws2 = ThisWorkbook.Sheets("Contacts") ' CHANGE THIS TO THE NEW IA COMPANY LOGBOOK
    Set b = ws2.Range("y20000:aa39999")
    
    Application.ScreenUpdating = False
    
    a = InputBox("What is the IA's name you are adding?     HINT: You can type ROB to look for Robert James.", "IA Name", Rob)
    If a = "" Or a = False Then
        GoTo 100
    End If
    With b
        Set LastCell = .Cells(.Cells.Count)
    End With
    lCount = 1
    Set FoundCell = b.Find(what:=a, LookAt:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    Do Until FoundCell Is Nothing
        FoundCell.EntireRow.Copy
        ws1.Range("y3000:aa3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
        Set FoundCell = b.FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
        lCount = lCount + 1
    Loop
    If lCount > 30 Then
        MsgBox ("Too many matches......Try narrowing your search.  Be more specific")
        Exit Sub
    End If
    ws1.Range("ab2998") = "IANAME1"
    100
    ws1.Protect "master32"
    Application.ScreenUpdating = True
    ws1.Range("a3013").Select
    
    End Sub

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: pastespecial to first blank row in range

            FoundCell.EntireRow.Copy
            ws1.Range("y3000:aa3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
    It's copying an entire row, then trying to paste the row starting at column Y. An entire row wouldn't fit starting at column Y as it does with column A.

    Try something like this, though I don't fully appreciate what the ultimate goal is.
            FoundCell.Resize(,3).Copy
            ws1.Range("Y3000:Y3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial xlPasteAll
    Note: this would still error if there were no blanks within the destination range.
    Last edited by AlphaFrog; 06-07-2013 at 12:31 AM.

  3. #3
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: pastespecial to first blank row in range

    Thanks AlphaFrog. That works better, but it is not copying the entire row. Essentially, the goal is to look up info from inputbox (which is located in column "y") and copy and paste the entire row to sheets "template". It populates a list with checkboxes to select which row the user wants to use on sheets "Template". Note that the first few columns (up to column "Q" in "Contacts) do not have any relevant info in them (they may or may not be populated). They may, but it is not relevant. I need the info from column "R" through column "AA" to be pasted.

    Thank you for your help.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: pastespecial to first blank row in range

    This would Copy\Paste the entire row.

            FoundCell.EntireRow.Copy
            ws1.Range("Y3000:Y3111").SpecialCells(xlCellTypeBlanks).Cells(1).EntireRow.PasteSpecial xlPasteAll

  5. #5
    Registered User
    Join Date
    12-03-2012
    Location
    Omaha, Nebraska
    MS-Off Ver
    2010
    Posts
    39

    Re: pastespecial to first blank row in range

    Thank you very much. That got it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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