Very new to VB (3 days)..I have a workbook that contains 3 worksheets. I have code for populating information from the first sheet into the second. Now I am trying to do the same thing with the 3rd sheet. The problem I am having is that in the first sheet I am taking the values of Col A, B, C and F and populating into Col A, B, C and D only if Col F in the first sheet has an entry and at the same time does not give me blank rows on the sheet I am populating. Here is the code:
Private Sub CommandButton1_Click()
Dim PopEnvCharges()
If ActiveSheet.Name = "EnvelopeCharges" Then
ActiveWorkbook.ActiveSheet.Range("a2:d65536").ClearContents
Dim EnvQuant As Object
Dim EnvChgCell As Object
Set EnvQuant = ActiveWorkbook.Sheets("Chargebacks").Range("F2")
Set EnvChgCell = ActiveWorkbook.Sheets("EnvelopeCharges").Range("A2")
Do Until IsEmpty(EnvQuant.Offset(0, -5))
If EnvQuant.Value <> "" And EnvQuant.Value <> 0 Then
Do Until IsEmpty(EnvChgCell)
Set EnvChgCell = EnvChgCell.Offset(1, 0)
Loop
EnvChgCell.Value = EnvQuant.Offset(0, -5)
EnvChgCell.Offset(0, 1).Value = EnvQuant.Offset(0, -4).Value
EnvChgCell.Offset(0, 2).Value = EnvQuant.Offset(0, -3).Value
EnvChgCell.Offset(0, 3).Value = EnvQuant.Value
End If
Set EnvQuant = EnvQuant.Offset(1, 0)
Loop
End If
End Sub
I want to do the same thing, but now I am populating from Sheet 1 Col A and B into Col A and B of Sheet 3 only if Col N on Sheet 1 has an x in the cell. Can someone help me with the code? This offset thing really has be stumped. Can anyone explain it?
Private Sub CommandButton1_Click()
Dim PopEnvCharges()
If ActiveSheet.Name = "EnvelopeCharges" Then
ActiveWorkbook.ActiveSheet.Range("a2:d65536").ClearContents
Dim EnvQuant As Object
Dim EnvChgCell As Object
Set EnvQuant = ActiveWorkbook.Sheets("Chargebacks").Range("F2")
Set EnvChgCell = ActiveWorkbook.Sheets("EnvelopeCharges").Range("A2")
Do Until IsEmpty(EnvQuant.Offset(0, -5))
If EnvQuant.Value <> "" And EnvQuant.Value <> 0 Then
Do Until IsEmpty(EnvChgCell)
Set EnvChgCell = EnvChgCell.Offset(1, 0)
Loop
EnvChgCell.Value = EnvQuant.Offset(0, -5)
EnvChgCell.Offset(0, 1).Value = EnvQuant.Offset(0, -4).Value
EnvChgCell.Offset(0, 2).Value = EnvQuant.Offset(0, -3).Value
EnvChgCell.Offset(0, 3).Value = EnvQuant.Value
End If
Set EnvQuant = EnvQuant.Offset(1, 0)
Loop
End If
End Sub
I want to do the same thing, but now I am populating from Sheet 1 Col A and B into Col A and B of Sheet 3 only if Col N on Sheet 1 has an x in the cell. Can someone help me with the code? This offset thing really has be stumped. Can anyone explain it?