PDA

View Full Version : VBA - Copy/Paste in new sheet + delete blank cells



qiceto
05-13-2017, 09:02 AM
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.

19166

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

mdmackillop
05-13-2017, 10:17 AM
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! :thumb

A very similar question just answered here (http://www.vbaexpress.com/forum/showthread.php?59423-copying-data-from-worksheet-to-another). Let us know if you need more assistance.

rlv
05-13-2017, 10:37 AM
With VBA there are many ways to accomplish this. One example attached.

qiceto
05-13-2017, 12:11 PM
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:
19168

I am continuing with the fight.

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

Thank you very much!

rlv
05-13-2017, 12:48 PM
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
05-14-2017, 05:34 AM
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
05-15-2017, 09:13 PM
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