+ Reply to Thread
Results 1 to 2 of 2

if copy target range full - copy to new range

Hybrid View

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    if copy target range full - copy to new range

    the attached code copys data entries in a worksheet to another worksheet of the same name in a different workbook. Entering the letter m in column "c" is the control. At present the macro copies the information to the next available row between rows 3 and 14 - there is a printable worksheet from rows 14 to 35

    how can I instruct the macro, that in the instance of not enough rows being available for the data being copied, that it will continue to copy to the range below row 35

    Sub Macro2()
    
    
    Dim rng As Range
    Dim rfiltered As Range
    Dim path, ws_name As String
    
    ws_name = ActiveSheet.Name
    ScreenUpdating = False
    
    If Application.WorksheetFunction.CountA(Range("c4:c330")) = 0 Then
        MsgBox "No Data highlighted!"
        Exit Sub
    End If
    
    
    MsgBox "Make Sure Maintenance File is closed before clicking OK - "
    
    
    
    
    
        With ActiveWorksheet
    1.    Set rng = Range("a3:t" & Range("a65500").End(xlUp).Row) - Filters range set by column c entries of 'm'    
    
    rng.AutoFilter Field:=3, Criteria1:="M"
    
    2.   Range("C:C,D:D").Select - Hides 2 columns not to be copied
        Selection.EntireColumn.Hidden = True
          path = Mid(ActiveWorkbook.FullName, 1, Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.Name))
          
        
    3.   Range("a4:j" & Range("c65500").End(xlUp).Row).Copy - Copies the range by the number of instances of 'm'
     
    
        
            Workbooks.Open ("p:\My Documents\truck files 2008\maintenance issues.xlsm")
        
                Sheets(ws_name).Activate
    4.           NextRow = Range("B14").End(xlUp).Row + 1 - Selects next empty row in column B of worksheet in opened workbook
                Range("B" & NextRow).PasteSpecial xlPasteValues
                
                
     5. -Would like to ensure that if all rows in range b3:B14 are full that the macro will defer to the next available row after row 35
    
          
        Dim Ans As Long
        
        Ans = MsgBox("Maintenance will now close, Save Changes?", vbYesNo)
        
        If Ans = vbYes Then
        ActiveWorkbook.Close savechanges:=True
        ElseIf Ans = vbNo Then
        ActiveWorkbook.Close savechanges:=False
        
        End If
        
        
    
       Range("C:C,D:D,F:F").EntireColumn.Hidden = False
       Range("c4:c" & Range("c65500").End(xlUp).Row).FormulaR1C1 = "mu"
       rng.AutoFilter
     
    
    
        
    
        End With
    
    
    End Sub
    Is there a way to do this, any pointers appreciated. I have searched extensively but have been unable to find any similar instance

    Rgds Nigelog
    Last edited by nigelog; 06-06-2012 at 09:33 AM.

  2. #2
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Re: if copy target range full - copy to new range

    Question unanswered - changes made to code and intention

    Rgds Nigelog

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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