• Status: Solved
• Priority: Medium
• Security: Public
• Views: 288

HOW DO I CORRECT THE CODE TO COPY CORRECTLY

I am copying the values from a row on one tab to a row on another tab based on a criteria. When I step thru the code I can see the source value being picked up but not the destination value being entered.
Can you point me in the right direction?
``````Set srcSheet = ThisWorkbook.Worksheets("CSM")
Set dstSheet = ThisWorkbook.Worksheets(1)
Set R = srcSheet.Range("B2")
i = 0
Set R = srcSheet.Range("B:B").Find(Right(dstSheet.Range("B15").Value, 5), R)
If Not R Is Nothing Then
foundRows(i) = R.Row
dstSheet.Cells(5 + i, 3).Value = srcSheet.Cells((foundRows(i)), 8)
dstSheet.Cells(5 + i, 4).Value = srcSheet.Cells((foundRows(i)), 9)
dstSheet.Cells(5 + i, 5).Value = srcSheet.Cells((foundRows(i)), 3)
dstSheet.Cells(5 + i, 6).Value = srcSheet.Cells((foundRows(i)), 4)
dstSheet.Cells(5 + i, 7).Value = srcSheet.Cells((foundRows(i)), 6)
Else
dstSheet.Range("C" & (5 + i) & ":H" & (12 + i)).ClearContents
dstSheet.Range("C" & (5 + i)).Formula = "N/A"
End If
Else
dstSheet.Range("C" & (5 + i) & ":H" & (12 + i)).ClearContents
dstSheet.Range("C" & (5 + i)).Formula = "N/A"
Set R = srcSheet.Range("B2")
End If

'srcBook.Close (False)
'Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
``````
0
llawrenceg
• 4
• 4
1 Solution

Commented:
Post all your code, and, ideally, a sample workbook.
0

Commented:
hmmm...Set dstSheet = ThisWorkbook.Worksheets(1)

Right after this, try to see which sheet was picked up:

Msgbox dstSheet.Name

Also, it's good practice at the end to clear the objects:

Set R = Nothing
0

Author Commented:

``````Sub CSM() ' Copies CSM data
Dim srcBook As Workbook
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim R As Range
Dim foundRows(2) As Long, i As Long

'Application.EnableEvents = False
Application.ScreenUpdating = False
On Error GoTo crash

Set srcSheet = ThisWorkbook.Worksheets("CSM")
Set dstSheet = ThisWorkbook.Worksheets(1)
Set R = srcSheet.Range("B2")
i = 0
Set R = srcSheet.Range("B:B").Find(Right(dstSheet.Range("B15").Value, 5), R)
If Not R Is Nothing Then
foundRows(i) = R.Row
dstSheet.Cells(5 + i, 3).Value = srcSheet.Cells((foundRows(i)), 8)
dstSheet.Cells(5 + i, 4).Value = srcSheet.Cells((foundRows(i)), 9)
dstSheet.Cells(5 + i, 5).Value = srcSheet.Cells((foundRows(i)), 3)
dstSheet.Cells(5 + i, 6).Value = srcSheet.Cells((foundRows(i)), 4)
dstSheet.Cells(5 + i, 7).Value = srcSheet.Cells((foundRows(i)), 6)
Else
dstSheet.Range("C" & (5 + i) & ":H" & (12 + i)).ClearContents
dstSheet.Range("C" & (5 + i)).Formula = "N/A"
End If
Else
dstSheet.Range("C" & (5 + i) & ":H" & (12 + i)).ClearContents
dstSheet.Range("C" & (5 + i)).Formula = "N/A"
Set R = srcSheet.Range("B2")
End If

'srcBook.Close (False)
'Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

crash:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox Err.Description
On Error GoTo 0

End Sub
``````
0

Commented:
First, a minor memory leak...replace this:

...
Exit Sub

crash:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox Err.Description
On Error GoTo 0
...

with this:

nocrash:

Exit Sub

crash:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox Err.Description
Resume nocrash

--
Confirm that dbSheet is the tab that has the data you want to read.
0

Author Commented:
dbSheet is not any tab in theworkbok. there ar only 4 tabs.... one is CSM
SHEET(1)IS NAMED DIFFERENTLY ....BASED ON user name
0

Commented:
I'm running in 2007 and when I start I immediate get and error and had to change lines to the following:

Set srcSheet = Worksheets("CSM")
Set dstSheet = Worksheets(1)

(without the Workbook reference.)
...
continuing...
since you didn't deliver a workbook, I have no data to run against.
Can yo attach a workbook without proprietary information in it?
What line are you seeing the information not appear?
Are you using the debug tool Shift-F9 to examine any of the pieces?

0

Author Commented:
Sorry about the workbook . It's all proprietary Client information
First the code works to the msg box
Set R = srcSheet.Range("B:B").Find(Right(dstSheet.Range("B15").Value, 5), R)' picks up the correct value of R from cell B15
If Not R Is Nothing Then ' the value  of R changes to the value of Range (B16")

dstSheet.Cells(5 + i, 3).Value = srcSheet.Cells((foundRows(i)),8) " gete the correct value from source
dstSheet.Cells(5 + i, 4).Value = srcSheet.Cells((foundRows(i)), 9)
dstSheet.Cells(5 + i, 5).Value = srcSheet.Cells((foundRows(i)), 3)
dstSheet.Cells(5 + i, 6).Value = srcSheet.Cells((foundRows(i)), 4)
dstSheet.Cells(5 + i, 7).Value = srcSheet.Cells((foundRows(i)), 6)
0

Author Commented:
Finally figured it out . There were 3 hidden worksheets on the workbook and the code was running against the hidden sheets-- dbsheet was a hidden sheet.
Once I delete the hidden sheets, everything worked
0

Commented:
Excellent!  Glad you figured it out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.