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

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
    If i = 0 Or Not foundRows(i) = foundRows(0) Then
    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

Open in new window

0
llawrenceg
Asked:
llawrenceg
  • 4
  • 4
1 Solution
 
StephenJRCommented:
Post all your code, and, ideally, a sample workbook.
0
 
rspahitzCommented:
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
 
llawrencegAuthor Commented:
The MSGbox reads "dbSheet".
 
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
    If i = 0 Or Not foundRows(i) = foundRows(0) Then
    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

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rspahitzCommented:
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
 
llawrencegAuthor 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
 
rspahitzCommented:
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
 
llawrencegAuthor 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
 
llawrencegAuthor 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
 
rspahitzCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now