Copy paste non blank cells VBA - issue with formulas cells

[Lourogm]

Hi,

I'm new to working with VBA and am trying to do a VBA that copies the data I have on a list and pastes it onto a different worksheet. I intend to use it as a sort of constant inventory, so you only have to feed it inputs and outputs, activate the macro and it is stored in the inventory sheet.

I'm currently trying this approach:


Sub Macro1()
'
' Macro1 Macro
'
Worksheets("Sheet1").Range("B6:D22").SpecialCells(xlCellTypeConstants).Copy
Worksheets("Inventário").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Worksheets("Sheet1").Range("C6:D22").ClearContents

End Sub<


"B" column cells have a formula that automatically generates the date value with this formula "=IF(C6=0;"";TODAY())" so it has a constant formatting and people don't have to input it by hand next to every item.

I'm having an issue because the macro does not copy and paste the values from "B" column cells.

I've been looking for ways to do it and i've seen you need to be careful with cells that have formulas in them when copy pasting, but can't seem to find exactly how to do it.

Help would be greatly appreciated.

Thank you!

 

[VeBeYay]

Hi,
I think the prob is the "SpecialCells(xlCellTypeConstants)." bit. The B cells aren't a constant 'cos they're calculated by a formula.
Try removing that bit from the copy command.
The "xlPasteValues" ensures only values are copied without the formulae so you have that covered already.

 

[Lourogm]

Hi,
I think the prob is the "SpecialCells(xlCellTypeConstants)." bit. The B cells aren't a constant 'cos they're calculated by a formula.
Try removing that bit from the copy command.
The "xlPasteValues" ensures only values are copied without the formulae so you have that covered already.

You are correct, but if I erase that it copies all cells in the interval, i suspect since cells in the "B" column aren't really blank (they have formulas). So even though it doesn't copy the formulas,even if I only have line 6 filled it still copies the whole B6:D22 range and the list gets filled with the blanks from B7:D22 and the next time I run the operation it will paste the values to line 19, after all the blanks it copied.

 

[VeBeYay]

Can you maybe post an example of your sheet and an example of what you hope to get from it please?

If the cell in col B is blank, do you still need the cells from col's C & D for that row copied?

 

[Lourogm]

Sure,

My current sheet where the values are input is this one.

The cells in the B column are the ones where date is set automatically. I basically want a macro that is connect to the "entrada" button that copies the values that are input in each column to a different sheet. I need them to be copied sequentially (hence the Worksheets("Inventário").Range("B" & Rows.Count).End(xlUp).Offset(1)) and without blanks between them so I can then easily retrieve the stock amount in a given date. The list where the values are copied to worksheet "inventário" is supposed to grow over time. A given worker only has to input the product and quantity and click the button and the list is automatically updated.

I'm new to programming, I hope I was able to be clear. Do you need anything else?

Thank you!

 

[VeBeYay]

OK, give this a try.

Sub Inv()

Dim iInvNxtRow, iLRow, i As Integer

iLRow = Worksheets("Sheet1").Cells(Cells.Rows.Count, 2).End(xlUp).Row
iInvNxtRow = Worksheets("Inventário").Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1

For i = 6 To iLRow
    If Worksheets("Sheet1").Cells(i, 2) <> "" Then
        Worksheets("Sheet1").Cells(i, 2).Resize(, 3).Copy
        Worksheets("Inventário").Cells(iInvNxtRow, 2).PasteSpecial xlPasteValues
        iInvNxtRow = iInvNxtRow + 1
    End If
Next

Worksheets("Sheet1").Cells(6, 3).Resize(iLRow, 2).Clear

Application.CutCopyMode = False

End Sub

In case you don't already, might be worth making a copy of the sheet1 data before running this each time so you have a backup if the system should happen to glitch.

 

[Lourogm]

Hi VeBeYay,

It works but now it clears the drop down menus I had on the C column so they select the products from a fixed list and don't have to type them. I forgot to mention them, sorry if i'm being a nuisance! I'm guessing it has to do with "Worksheets("Sheet1").Cells(6, 3).Resize(iLRow, 2).Clear" though I don't yet fully understand the code you gave me.

Any ideas?

Thank you!

 

[Lourogm]

I just added Contents at the end so it only cleared contents and it's working perfectly! Thank you!

 

[Lourogm]

Ok, now I'm trying to add a second test where If Worksheets("Sheet1").Cells(i, 2) <> "" it tests to see if all cells in range are non empty (to make sure they input properly product, date and quantity and don't forget any to input a value). I'm trying with .CountA , but i don't seem to understand how nested if's work (i'm guessing that's where my problem is). I also don't understand why I need to have the Next there and what to have after it.

Here's my clumsy patchwork of code:

Sub InvInput()


Dim iInvNxtRow, iLRow, i As Integer


iLRow = Worksheets("Sheet1").Cells(Cells.Rows.Count, 2).End(xlUp).Row
iInvNxtRow = Worksheets("Inventário").Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1


For i = 6 To 22
If Worksheets("Sheet1").Cells(i, 2) <> "" Then
If Application.CountA.Worksheets("Sheet1").Cells(i, 2).Resize(, 3) < 3 Then
MsgBox "Dados mal preenchidos"
Else: Worksheets("Sheet1").Cells(i, 2).Resize(, 3).Copy
Worksheets("Inventário").Cells(iInvNxtRow, 2).PasteSpecial xlPasteValues
Worksheets("Sheet1").Cells(i, 2).Resize(, 3).ClearContents
iInvNxtRow = iInvNxtRow + 1
End If
Next



Application.CutCopyMode = False


End Sub


Help would be much appreciated!

Thank you.

 

[VeBeYay]

Hi,

I've added the test in to the earlier code.

Sub Inv()

Dim iInvNxtRow, iLRow, i As Integer

iLRow = Worksheets("Sheet1").Cells(Cells.Rows.Count, 2).End(xlUp).Row
'iInvNxtRow = Worksheets("Inventário").Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1

For i = 6 To iLRow
    If Application.CountA(Worksheets("Sheet1").Cells(i, 2).Resize(, 3)) <> 3 Then
        MsgBox "Dados mal preenchidos"
        'Exit Sub
    Else
        Worksheets("Sheet1").Cells(i, 2).Resize(, 3).Copy
        Worksheets("Inventário").Cells(iInvNxtRow, 2).PasteSpecial xlPasteValues
        iInvNxtRow = iInvNxtRow + 1
    End If
Next

Worksheets("Sheet1").Cells(6, 3).Resize(iLRow, 2).Clear

Application.CutCopyMode = False

End Sub

The "Next" is the end of the for - next loop so first run through, i = 6. When it reaches "Next", it goes back to the "For" line, increases i to 7 and repeats until i exceeds the upper limit (22).
Similarly, each "If" must have an "End If" associated with it. Indentation (tab) can help to see where each section starts and ends.

So, with this new bit of code, the "If" tests to see if all 3 cells are completed. If not, msgbox warning, otherwise (Else) it continues. A point to consider, do you want to continue if the cells aren't completed or should the macro end there (see the Exit Sub command I've included but commented out) to give the user a chance to correct the error? Maybe this "error" test should be a separate loop in the macro that runs before the copy process even starts?
Have fun.