+ Reply to Thread
Results 1 to 3 of 3

Copy Conditional Selections and Paste in Empty Cells Specified

Hybrid View

  1. #1
    Registered User
    Join Date
    03-24-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Copy Conditional Selections and Paste in Empty Cells Specified

    Hi! I have been working on a macro that looks at a table on another worksheet, and selects certain cells based on the criteria provided in the code. I would like for the information to then be pasted onto a new worksheet, however I cannot find a way to write the code that will start on row 92 and list it on down until the identified selections have been all listed. Anything online is using the END.(xlsup) funtion which puts it at the end of all the data on the worksheet (which again will not work as there is data that continues after the area I would like this information posted to). I tried a for loop, but then that just repeated the next value found in the orginial list several times... I am not sure what to try next :S thanks for the help in advance!

    Below is the code:

    
    Sub fortyfoot()
    
    Dim wb As Worksheet: Set wb = ActiveWorkbook.Sheets("Current Loads")
    Dim wb2 As Worksheet: Set wb2 = ActiveWorkbook.Sheets("Plant Overview")
    Dim rCell As Range
    
    Application.ScreenUpdating = False
    Worksheets("Plant Overview").Range("b92:d103").ClearContents
    
    For Each rCell In wb.Range("B1:B" & wb.Range("B" & Rows.Count).End(xlUp).Row)
        If rCell.Value = wb2.Range("$C$41") And rCell.Offset(0, 2).Value = "40'" And rCell.Offset(0, 3).Value = "EMPTY" And rCell.Offset(0, 13).Value > 5 Then
            wb.Range("C" & rCell.Row).Copy
            If wb2.Range("b92").Value = "" Then
                wb2.Range("b92").PasteSpecial xlPasteValues
            Else
              For i = 93 To 103
                 wb2.Cells(i, 2).PasteSpecial xlPasteValues
                 Next i
            End If
        End If
        If rCell.Value = wb2.Range("$C$41") And rCell.Offset(0, 2).Value = "40'" And rCell.Offset(0, 3).Value = "EMPTY" And rCell.Offset(0, 13).Value > 5 Then
            wb.Range("O" & rCell.Row).Copy
            If wb2.Range("d92").Value = "" Then
                wb2.Range("d92").PasteSpecial xlPasteValues
            Else
            For i = 93 To 103
               wb2.Cells(i, 4).PasteSpecial xlPasteValues
               Next i
            End If
        End If
    
       
    Next rCell
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub

  2. #2
    Registered User
    Join Date
    03-24-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy Conditional Selections and Paste in Empty Cells Specified

    any thoughts?

  3. #3
    Registered User
    Join Date
    03-24-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Copy Conditional Selections and Paste in Empty Cells Specified

    Ok I am getting closer just playing with it, but I still have an issue of repeated values. When the rCell value is pasted into wb2 it is repeating all the way from row 93 to 102. How can I stop it from duplicating? Here is my updated code:

    Sub fortyfoot()
    
    Dim wb As Worksheet: Set wb = ActiveWorkbook.Sheets("Current Loads")
    Dim wb2 As Worksheet: Set wb2 = ActiveWorkbook.Sheets("Plant Overview")
    Dim rCell As Range
    
    Application.ScreenUpdating = False
    Worksheets("Plant Overview").Range("b92:d103").ClearContents
    
    
    
    For pasterowindex = 92 To 103
    For Each rCell In wb.Range("B1:B" & wb.Range("B" & Rows.Count).End(xlUp).Row)
       
        If rCell.Value = wb2.Range("$C$41") And rCell.Offset(0, 2).Value = "40'" And rCell.Offset(0, 3).Value = "EMPTY" And rCell.Offset(0, 13).Value > 5 Then
           
            wb.Range("C" & rCell.Row).Copy
        
            wb2.Range("B" & pasterowindex).End(xlUp).Offset(1, 0).PasteSpecial xlValues
            
            wb.Range("O" & rCell.Row).Copy
            
            wb2.Range("D" & pasterowindex).End(xlUp).Offset(1, 0).PasteSpecial xlValues
            
          
        End If
    
      
        
    Next rCell
    
      pasterowindex = pasterowindex + 1
        Next pasterowindex
       
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA to copy non empty cells then paste to next available cell in another worksheet
    By Chrisbrough88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2014, 08:07 AM
  2. [SOLVED] Copy and Paste Cells to Next empty cell in range
    By oddinho2 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-20-2013, 12:51 PM
  3. Copy multiple cells then paste in the next empty row
    By D310633 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2013, 10:01 AM
  4. VBA to copy selected cells down to empty cell and paste
    By rioutousgnome in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-28-2013, 01:55 PM
  5. [SOLVED] Macro to Copy cells data and paste on next empty row
    By tfaridi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2012, 05:35 PM

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