Conditional copy and paste using macro [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

Just want to seek help to anyone about my problem about copying and paste multiple cells from sheet 1 to new sheet hru new sheet also with designated raw and colunm as below example.. copying and pasting the details needed if depending the ROW.

My concern is, i assign cells must be copying depends if the column F is greater than zero.
below is my formula..

With historyWks
FinalRow = Range("F65536").End(xlUp).Row
' Find the last row of data
' Loop through each row
For X = 2 To FinalRow
' Decide if to copy based on column F
ThisValue = Range("F" & X).Value
' could be any value
If ThisValue > "0" Then
Worksheets("sheet1").Range("A" & X & ":E" & X).Copy
nextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & nextRow).PasteSpecial
Worksheets("sheet2").Select
End If
Next X

End With

then the below formula is the details need to copy and paste in the column A to E



With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
sheet2.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell




value of the my cell is the value in sheet 1 for cell G4,G9,B9

Hope it will make sense.

Your usual help and support will hihgly appreicate.


marvs

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
suggestion

instead of looping through autofilter and copy the visible cells to any other location. experiment manually and then write the macro. every time you have to remove the filter.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!