VBA - Copy/Paste in new sheet + delete blank cells

[qiceto]

Hello Dears,

I have the following scenario:

In column A i have reference numbers (text) with description above them, in column C I have quantities for some of the reference numbers. My goal is when I click a simple button, to go on a second sheet where i have only the reference numbers (without the description) with the quantities (removing all zero or blank cells). You can see what i want to achieve on the table bellow.

I have no knowledge in VBA, I spend all day digging in the forums and I only achieved to create a button saying "Hello", when you click it.

Capture.jpg

Many thanks in advance for the ones who can help me!

[mdmackillop]

I spend all day digging in the forums and I only achieved to create a button saying "Hello", when you click it.

I think I'll use that for my signature!

A very similar question just answered here. Let us know if you need more assistance.

[rlv]

With VBA there are many ways to accomplish this. One example attached.

[qiceto]

Hello,

thank you both for your quick reply.

Currently I am trying to adjust the code of rlv, but i face two problems.

So first thing is that the table i want to select is not starting from row 1, but from row 29 to row 89, columns A-J. Reference numbers are in column A, quantities are in Column G.So I acctually don't want to copy paste the whole sheet, with hidden rows and columns, but instead only $A$29:$A$89 and $G29:$G$89.
Above I have other things I can not remove.

Private Sub CommandButton1_Click()
    CopyInfo
End Sub

Sub CopyInfo()
    Application.ScreenUpdating = False
    Worksheets("Sheet2").UsedRange.ClearContents
    With Worksheets("Sheet1")
        If .AutoFilterMode Then
            .AutoFilterMode = False
        End If

        .Columns.AutoFilter Field:=Me.Columns("G").Column, Criteria1:="<>"
        .Columns("B29:B89").Hidden = True
        .Columns("C29:C89").Hidden = True
        .Columns("D29:D89").Hidden = True
        .Columns("E29:E89").Hidden = True
        .Columns("F29:F89").Hidden = True  ***** something like this I think, but in this case it doesn not hide the at all...*****
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy

        With Worksheets("Sheet2")
            .Range("A1").PasteSpecial Paste:=xlPasteValues
            .Columns.AutoFit
        End With

        .AutoFilterMode = False
        .UsedRange.Columns.Hidden = False
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

And second thing, is when I run the code it doesn't copy to the second sheet, but it is just hidding everything different from zero in column G on the same sheet.

Also it gives me this error:
Capture2.jpg

I am continuing with the fight.

If you have any solution about this, I appreciate if you share!

Thank you very much!

[rlv]

The purpose of an example is to illustate a method or an approach to coding that you can then adapt to your purposes. Pardon me for saying so, but when you said you had spent all day, and only managed to create a button saying "Hello", I was concerned that perhaps you might not have enough VBA background to apply any suggestions you receive here to your actual workbook; but I also understand that we all have to start somewhere. Rather than have you describe the many ways that your actual workbook and your actual data is different from the example you originally posted, it would be better if you can create a workbook that has the data arranged exactly as you want it, along with an example of how you want the result to look and any code you have already written to perform the function and then include your workbook in your post as an attachment. That makes it easier for others to assist.

[qiceto]

Hello,

finally, after one more day digging in the forums and reading, I managed to assamble this, which works for me.

Private Sub CommandButton1_Click()    CopyInfo
    Filter
    RemoveHiddenRows
    
End Sub




Sub CopyInfo()


Application.ScreenUpdating = False


    Sheets("AS-P").Range("A29:A88").Copy
    Sheets("Specification").Range("A2").PasteSpecial Paste:=xlPasteValues
    
    Sheets("AS-P").Range("G29:G88").Copy
    Sheets("Specification").Range("B2").PasteSpecial Paste:=xlPasteValues
     
     Application.ScreenUpdating = True
   
   End Sub


Sub Filter()


With Sheets("Specification")


            .AutoFilterMode = False


            .Range("B:B").AutoFilter Field:=1, Criteria1:=">0", visibledropdown:=False


    End With
End Sub


Sub RemoveHiddenRows()


    Dim oRow As Range, rng As Range
    Dim myRows As Range
    
    With Sheets("Specification")
    
        Set myRows = Intersect(.Range("A:B").EntireRow, .UsedRange)
        If myRows Is Nothing Then Exit Sub
    End With


    For Each oRow In myRows.Columns(2).Cells
        If oRow.EntireRow.Hidden Then
            If rng Is Nothing Then
                Set rng = oRow
            Else
                Set rng = Union(rng, oRow)
            End If
        End If
    Next


    If Not rng Is Nothing Then rng.EntireRow.Delete
End Sub


Private Sub ExportBOM_Click()


End Sub

Thank you for the help!

[jolivanes]

Would the result of this be what you expect?
Change references as required

Sub With_Array()
Dim a, i As Long, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1:B1").Value = Array("Ref #", "Qty")
a = Range("A2:C" & lr).Value
    For i = LBound(a) To UBound(a)
        If a(i, 3) <> "" Then
            With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                .Value = a(i, 1)
                .Offset(, 1).Value = a(i, 3)
            End With
        End If
    Next i
End Sub