Solved

# HOW DO I CORRECT THE CODE TO COPY CORRECTLY

Posted on 2011-05-06
Medium Priority
282 Views
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
Question by:llawrenceg
• 4
• 4

LVL 24

Expert Comment

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

LVL 22

Expert Comment

ID: 35706479
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 Comment

ID: 35708034

``````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

LVL 22

Expert Comment

ID: 35708081
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 Comment

ID: 35709287
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

LVL 22

Accepted Solution

rspahitz earned 2000 total points
ID: 35711001
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 Comment

ID: 35713374
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 Comment

ID: 35713382
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

LVL 22

Expert Comment

ID: 35714228
Excellent!  Glad you figured it out!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youāll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dā¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.