Copy Columns A to I Problem
 

I have code that Die_Another_Day helped me with, but it only copies the Data
in Column A to the worksheet.
I need to have the data in the row, but Columns A to I in each row copied.
I'm SO tired...I just can't figure out what the problem is.
Can someone let me go to sleep by helping me with this?
Here is the code:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" & Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And .Range("A" _
& cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow) '<< I
suspect this is the line where the problem is!
End If
Next
End With


End Sub

Copy Columns A to I Problem
 

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And _
.Range("A" & cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Resize(, 9).Copy Sheets("sheet1").Range("A" &
nRow)
End If
Next
End With


End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...
I have code that Die_Another_Day helped me with, but it only copies the
Data
in Column A to the worksheet.
I need to have the data in the row, but Columns A to I in each row copied.
I'm SO tired...I just can't figure out what the problem is.
Can someone let me go to sleep by helping me with this?
Here is the code:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And
..Range("A" _
& cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow) '<< I
suspect this is the line where the problem is!
End If
Next
End With


End Sub

Copy Columns A to I Problem
 

Seems I have a NEW GURU on board!
Worked perfect first time! Thank you so much. Now I need to go through it
and understand why I couldnt' make it work. THANK YOU AGAIN!!

"Bob Phillips" wrote:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And _
.Range("A" & cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Resize(, 9).Copy Sheets("sheet1").Range("A" &
nRow)
End If
Next
End With


End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...
I have code that Die_Another_Day helped me with, but it only copies the
Data
in Column A to the worksheet.
I need to have the data in the row, but Columns A to I in each row copied.
I'm SO tired...I just can't figure out what the problem is.
Can someone let me go to sleep by helping me with this?
Here is the code:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And
..Range("A" _
& cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow) '<< I
suspect this is the line where the problem is!
End If
Next
End With


End Sub

Copy Columns A to I Problem
 

Ah, the resize...thanks again...I'll never have to ask THAT question again :)

"Bob Phillips" wrote:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And _
.Range("A" & cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Resize(, 9).Copy Sheets("sheet1").Range("A" &
nRow)
End If
Next
End With


End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...
I have code that Die_Another_Day helped me with, but it only copies the
Data
in Column A to the worksheet.
I need to have the data in the row, but Columns A to I in each row copied.
I'm SO tired...I just can't figure out what the problem is.
Can someone let me go to sleep by helping me with this?
Here is the code:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And
..Range("A" _
& cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow) '<< I
suspect this is the line where the problem is!
End If
Next
End With


End Sub

Copy Columns A to I Problem
 

A very useful property

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...
Ah, the resize...thanks again...I'll never have to ask THAT question again
:)

"Bob Phillips" wrote:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And _
.Range("A" & cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Resize(, 9).Copy Sheets("sheet1").Range("A" &
nRow)
End If
Next
End With


End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" wrote in message
...
I have code that Die_Another_Day helped me with, but it only copies
the
Data
in Column A to the worksheet.
I need to have the data in the row, but Columns A to I in each row
copied.
I'm SO tired...I just can't figure out what the problem is.
Can someone let me go to sleep by helping me with this?
Here is the code:

Sub CopyData()

Dim LRow As Long 'Last Row
Dim nRow As Long 'Next Row to copy to
Dim cnt As Long
'Dim sourceRange As Range


LRow = Sheets("All_Jobs").Range("A" &
Sheets("All_Jobs").Rows.Count).End(xlUp)
With Sheets("All_Jobs")
For cnt = 7 To LRow
If .Range("A" & cnt) = Range("FilterCriteria").Cells(1) And
..Range("A" _
& cnt) <= Range("FilterCriteria").Cells(2) Then
nRow = Sheets("sheet1").Range("A" & _
Sheets("sheet1").Rows.Count).End(xlUp).Offset(1, 0).Row
'Set sourceRange = ActiveCell.EntireRow
'.Range("sourceRange").Copy Sheets("sheet1").Range("A" & nRow)
.Range("A" & cnt).Copy Sheets("sheet1").Range("A" & nRow) '<<
I
suspect this is the line where the problem is!
End If
Next
End With


End Sub