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!

 

[Lourogm]

Thanks for your explanation!

I'm getting an error though, and can't figure out why. When I try to run your macro I get a "run-time error '1004': Application-defined or object-defined error" and in debug it highlights this portion of the code: "Worksheets("Existências").Cells(iInvNxtRow, 2).PasteSpecial xlPasteValues"

I edited my post since I had changed the name of a worksheet from "inventário" to "Existências" and forgot to mention that here. I changed the code accordingly so it goes where it is supposed to go, and so replaced "Inventário" on that portion with "Existências", I apologise for the confusion.

 

[VeBeYay]

Oops, sorry, I forgot that I commented out the line below. Just remove the apostrophe from the start of the line.

'iInvNxtRow = Worksheets("Existências").Cells(Cells.Rows.Count, 2).End(xlUp).Row + 1