| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy Columns | Setting up and Configuration of Excel | |||
| A visual basic value copy BUG?? - accounting format has copy problem!! | Excel Programming | |||
| How do I copy columns between worksheets if the columns don't matc | Excel Worksheet Functions | |||
| Copy columns values into separate columns | Excel Discussion (Misc queries) | |||
| Copy a row from one sheet to another and not all columns copy | Excel Programming | |||
Hybrid Mode
