VBA Button to Copy Range of Cells and Save AS

[matthewrlong]

Hey Everyone,

I have been trying to figure out how to make a button that will copy specific rows (A6:L6 and down) from "Optimization Ticket".
This sheet could have 5 rows filled in or 50 rows filled in.
These rows will land in "Process Ticket" starting at cell A2.

This is what I have so far.
Trust me when I tell you, I am a novice.


Option Explicit


Sub CommandButton2_Click()
Dim lastRow As Long, i As Long
Dim CopyRange As Range


With Sheets("Optimization Ticket")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row


For i = 2 To lastRow
If Len(Trim(.Range("A" & i).Value)) <> 0 Then
If CopyRange Is Nothing Then
Set CopyRange = .Rows(i)
Else
Set CopyRange = Union(CopyRange, .Rows(i))
End If
Else
Exit For
End If
Next


If Not CopyRange Is Nothing Then
CopyRange.Copy Sheets("Process Ticket").Rows(1)
End If
End With
End Sub

Thank you
ML

 

[JackDanIce]

Try below (untested - backup your file before running code), it assumes header is on row 1 of sheet Optimization Tracker:

Sub CommandButton2_Click()
    
    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
        
    With Sheets("Optimization Ticket")
        If .AutoFilterMode Then .AutoFilter = False
        
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        y = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        If x = 1 Then Exit Sub
        
        With .Cells(1, 1).Resize(x, y)
            .AutoFilter field:=1, Criteria1:="<>"
            .Offset(1).Resize(x - 1).speciallcells(xlCellTypeVisible).Copy
        End With
        
    End With
    
    With Sheets("Process Ticket")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
    
End Sub

 

[matthewrlong]

Sorry for the delayed response. This didn't work.

It said that it did recognize the format or something.

here is the code I am using. Seems to work good. I just needed to mess around with the range.

Sub CommandButton2_Click()
Dim a As Integer
Dim ws As String

With Worksheets("Optimization Ticket")
For a = 6 To .Cells(.Rows.Count, 2).End(xlUp).Row
Select Case .Cells(a, 16).Value
Case Is = ""
ws = "Optimization Ticket"
End Select
.Range("B" & a & ":L" & a).Copy Worksheets("Process Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next a
End With
End Sub

Thanks ML