Hi All,
I am trying to move two columns of data from one sheet to another. Data in the first column to be moved is just text, while data in the second column is a value returned from a formula. I have got that part working. For the most part, the code below works, unless there is a a series of "Hazard Values" at the bottom of 3 or more.
I have also attached screengrabs of two different instances. Once where the copy and paste works, and the second where it doesn't.Private Sub RankHazards_Click() Dim sh1 As Worksheet Dim sh2 As Worksheet Dim lastrow1 As Long Dim lastrow2 As Long Dim j As Long Dim i As Long Dim rng As Range Set sh1 = wsAnalysis Set sh2 = wsRanking lastrow1 = sh1.Range("K" & sh1.Rows.count).End(xlUp).Row lastrow2 = sh2.Range("C" & sh2.Rows.count).End(xlUp).Row j = 2 For i = 0 To lastrow1 Set rng = sh1.Range("A2").Offset(i, 0) If Not (IsNull(rng) Or IsEmpty(rng)) Then sh1.Range("A" & i + 2).Copy sh2.Range("B" & j).PasteSpecial xlPasteValues sh1.Range("L" & i + 2).Copy sh2.Range("C" & j).PasteSpecial xlPasteValues j = j + 1 End If Next i Application.CutCopyMode = False Application.ScreenUpdating = False Range("C1") = "Hazard Value" Columns("B:C").Sort key1:=Range("C2"), _ order1:=xlDescending, Header:=xlYes End Sub
HAZNY - Analysis Page Works.JPG
HAZNY - Ranking Page Works.JPG
HAZNY - Analysis Page Doesn't Work.JPG
HAZNY - Ranking Page Doesn't Work.JPG
Interesting point, I just came to find out. If the Ranking Page has all the values in, such as the second image above, and I run the analysis and receive three or more zeroes at the bottom, in a row, it will copy and paste the values. So I am not sure why this is being so difficult.
Any help is greatly appreciated.