?
Solved

HOW DO I CORRECT THE CODE TO COPY CORRECTLY

Posted on 2011-05-06
9
Medium Priority
?
282 Views
Last Modified: 2012-05-11
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
Comment
Question by:llawrenceg
  • 4
  • 4
9 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35706238
Post all your code, and, ideally, a sample workbook.
0
 
LVL 22

Expert Comment

by:rspahitz
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

by:llawrenceg
ID: 35708034
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 22

Expert Comment

by:rspahitz
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

by:llawrenceg
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

by:
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

by:llawrenceg
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

by:llawrenceg
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

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question