VBA help needed

[bionicle]

Hi all,
I have to following VBA which works as its suppose to, its copying the data from one sheet to the other. how can I adjust it so it only copies and pastes the original cell value rather than the formula's, colours and formats?

any help would be great..

Private Sub CommandButton1_Click()
Dim lastrow As Long, Erow As Long

lastrow = Worksheets("Area").Cells(Rows.Count, 2).End(xlUp).Row
For i = 8 To lastrow
If Worksheets("Area").Cells(i, 22).Value = "Sale" Then
Worksheets("Area").Cells(i, 3).Copy
Erow = Worksheets("Cost").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 1)
Worksheets("Area").Cells(i, 7).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 2)
Worksheets("Area").Cells(i, 9).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 3)

End If

Next i

End Sub

 

[Joe4]

Since you are just copying one cell at a time, instead of doing a copy/paste, just do a straight value setting.
So, replace each block like this:

Worksheets("Area").Cells(i, 9).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 3)

with something like this:

Worksheets("Cost").Cells(Erow + 1, 3).Value = Worksheets("Area").Cells(i, 9).Value

and that will only copy the values over.

So copy that same technique for your other ranges too.

 

[bionicle]

That's great thank you so much

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[bionicle]

Me again,

I've changed my macro and it works fine, it lists all the information from Sheet1 to Sheet2 showing the extrapolation of the info its being asked to look for. All good.
however I'm trying to use the same VBA in another workbook (same info) but instead of it starting in row A2 I need it to start in row 12 (C12 to be precise). Can kinda make this happen however it just copies and pastes all the data into the one row rather than carrying on down the page.

How do I fix this issue? Any help would be appreciated.

Private Sub CommandButton1_Click()
Dim lastrow As Long, Erow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For i = 8 To lastrow
If Worksheets("Sheet1").Cells(i, 22).Value = "Yes" Then
Worksheets("Sheet1").Cells(i, 3).Copy
Erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Cells(Erow + 1, 1).Value = Worksheets("Sheet1").Cells(i, 2).Value
Worksheets("Sheet2").Cells(Erow + 1, 2).Value = Worksheets("Sheet1").Cells(i, 3).Value
Worksheets("Sheet2").Cells(Erow + 1, 3).Value = Worksheets("Sheet1").Cells(i, 7).Value

End If

Next i

End Sub

 

[Joe4]

Can you show us some examples of what you are trying to do, so we have a better understanding of your data structure and desired outcome?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.