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
Bookmarks