Copy value & offset from workbook X to workbook Y only when range value > 0, then loop for remaining rows

Question

Any and all help or suggestions are certainly appreciated... I'm working with two separate workbooks.

Workbook X is my "Order Form", this contains relevant columns: column A: item num column B: qty

I want to pull both the item number and the qty value ONLY IF qty > 0 or <> NULL.

This data will then be sent to Workbook Y, which contains: column B: item num column C: qty

Below is one variation of the code I've attempted, I'm about 5 hours into this and running low on ideas.

Thanks!

Dim x As Workbook
Dim y As Workbook

'Open both workbooks
Set x = Workbooks.Open(Me.txtSelect)
Set y = Workbooks.Open(Me.txtOutput)

Dim i As Integer
i = 1

While i < 200
'Extract data from sheet x into sheet y
If x.Sheets("Order Form").Cells(i, 2).Value > 0 Then
x.Sheets("Order Form").Cells(i, 1).Value = y.Sheets("UploadTemplate").Cells(B2 + i).Value
x.Sheets("Order Form").Cells(i, 2).Value = y.Sheets("UploadTemplate").Cells(C2 + i).Value
Else
End If
i = i + 1
Wend

Here was the original code I had in place of the "While", but was getting ALL values:

If x.Sheets("Order Form").Range("B9").Value > 0 Then
x.Sheets("Order Form").Range("A9:B200").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=x.Sheets("Order Form").Range("A11:B11"), CopyToRange:=y.Sheets("UploadTemplate").Range("B2:C2") _
    , Unique:=False
End If

I need to pull both values from A/B only when B contains a value that is not null/0. Right now, the code does not pull anything and the original code pulled the entire range regardless of the criteria, I think my If/Then is incorrect, but am not sure how to correct it. — MBrewer, Jul 14 '14 at 20:33

score 0 · Accepted Answer · answered Jul 15 '14 at 00:27

Try this:

Dim x As Workbook
Dim y As Workbook
dim yi as integer
'Open both workbooks
Set x = Workbooks.Open(Me.txtSelect)
Set y = Workbooks.Open(Me.txtOutput)

Dim i As Integer
i = 1
yi=2
While i < 200
    'Extract data from sheet x into sheet y
    If x.Sheets("Order Form").Cells(i, 2).Value > 0 Then
        y.Sheets("UploadTemplate").Cells(yi,2).Value=x.Sheets("Order Form").Cells(i, 1).Value
        y.Sheets("UploadTemplate").Cells(yi,3).Value=x.Sheets("Order Form").Cells(i, 2).Value
        yi = yi + 1
    End If
    i = i + 1
Wend

You need a second counter on the second workbook, as if you're skipping rows in the first workbook because they're blank, you don't want to do the same in the second workbook(I assume)

You also need to ensure that you taking the values from the first sheet, and assigning them to the second sheet. eg. Y gets the value from X - Y=X

This worked! Good to know I wasn't TOO far off, but frustrating as well after hours of searching online for an answer. Thanks RowanC, this is greatly appreciated :)! — MBrewer, Jul 15 '14 at 12:14

Copy value & offset from workbook X to workbook Y only when range value > 0, then loop for remaining rows

1 Answers1

Linked