U_Shrestha
02-28-2008, 12:35 PM
Hello,
I need a little help for the following code. xld had been helping me in this code. Basically, the following code copies and pastes the data from one worksheet to another worksheet. I just want the code to paste the values only instead of pasting the formulas, formats etc. Can someone please help? Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Const DestSheet As String = "DataPullUpPage"
Const SourceSheet As String = "AuditIssues"
Dim StationNo As Long
Dim FoundRow As Double
Dim DestRow As Double
Dim iSource As Double, iDest As Double, LastRow As Double
On Error Goto GetIssues_Error
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("c2")) Is Nothing Then
' Clear out old data
Me.Range("A12:A" & Me.Rows.Count).EntireRow.Delete
iSource = 2
iDest = 12
LastRow = Sheets(SourceSheet).Range("A" & Me.Rows.Count).End(xlUp).Row
' Find the first occurance of station number on the issues page.
While Sheets(SourceSheet).Cells(iSource, 1) <> Target.Value And iSource <= LastRow
iSource = iSource + 1
Wend
' If the station isn't found, exit
If iSource >= LastRow Then
MsgBox "Station Number does not exist"
Else
' Copy selected cells to the destination sheet
' Copy selected cells to the destination sheet
While Worksheets(SourceSheet).Cells(iSource, 1) = Target.Value
Sheets(SourceSheet).Range("C" & iSource & ":M" & iSource).Copy _
Me.Range("A" & iDest)
iSource = iSource + 1
iDest = iDest + 1
Wend
End If
End If
MyEnd:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
GetIssues_Error:
Resume MyEnd
End Sub
I need a little help for the following code. xld had been helping me in this code. Basically, the following code copies and pastes the data from one worksheet to another worksheet. I just want the code to paste the values only instead of pasting the formulas, formats etc. Can someone please help? Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Const DestSheet As String = "DataPullUpPage"
Const SourceSheet As String = "AuditIssues"
Dim StationNo As Long
Dim FoundRow As Double
Dim DestRow As Double
Dim iSource As Double, iDest As Double, LastRow As Double
On Error Goto GetIssues_Error
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("c2")) Is Nothing Then
' Clear out old data
Me.Range("A12:A" & Me.Rows.Count).EntireRow.Delete
iSource = 2
iDest = 12
LastRow = Sheets(SourceSheet).Range("A" & Me.Rows.Count).End(xlUp).Row
' Find the first occurance of station number on the issues page.
While Sheets(SourceSheet).Cells(iSource, 1) <> Target.Value And iSource <= LastRow
iSource = iSource + 1
Wend
' If the station isn't found, exit
If iSource >= LastRow Then
MsgBox "Station Number does not exist"
Else
' Copy selected cells to the destination sheet
' Copy selected cells to the destination sheet
While Worksheets(SourceSheet).Cells(iSource, 1) = Target.Value
Sheets(SourceSheet).Range("C" & iSource & ":M" & iSource).Copy _
Me.Range("A" & iDest)
iSource = iSource + 1
iDest = iDest + 1
Wend
End If
End If
MyEnd:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
GetIssues_Error:
Resume MyEnd
End Sub