Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
Hello,
I'm trying to automate a worksheet. I have a command button that calls a module, (Module2), the code selects a range of cells (Range("A7:I7") and copies that range to the next empty row of worksheet2 in the workbook. I can only get cell A7 to copy correctly, none of the remaining Range(B7:I7), copy over. The code is as follows: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range("A7:I7" & Paul).Value Next Paul End Sub I then tried: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("A7:I7") RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range(MyRange).Value Next Paul End Sub But that gives me a RunTime Error #1004 "Application-Defined" or "Object-Defined" error. Thanks in advance for any help, |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
Paul,
The reason on A7 is being copied across is because thats all you are asking it to do. Worksheets("sheet2").Range("A" & S2Row + Paul).Value selects only the cell A & (S2Row+Paul). Which in your case is A7. You need to amend this to be a range identical to the range you are copying from , i.e Worksheets("sheet2").Range("A7:I7").Value would work fine you have a loop running from 1 to RowToCopy, but you've declared RowToCopy to be 1, so essentially your loop is 'For Paul =1 to 1. You may want the option to copy multiple rows later which is why this is here. If not, try this for copying ranges. S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("a" & S2Row).Activate ActiveSheet.Paste hth David "Paul3rd" wrote in message ... Thanks for your reply Don, I'm having a bad day though; where in the code would I insert row+1? "Don Guillett" wrote: row+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul3rd" wrote in message ... Hello, I'm trying to automate a worksheet. I have a command button that calls a module, (Module2), the code selects a range of cells (Range("A7:I7") and copies that range to the next empty row of worksheet2 in the workbook. I can only get cell A7 to copy correctly, none of the remaining Range(B7:I7), copy over. The code is as follows: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range("A7:I7" & Paul).Value Next Paul End Sub I then tried: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("A7:I7") RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range(MyRange).Value Next Paul End Sub But that gives me a RunTime Error #1004 "Application-Defined" or "Object-Defined" error. Thanks in advance for any help, |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
Thanks David,
I changed the code to: Sub movedata() Dim S2Row As Long Dim Copies As Integer Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 For Paul = 1 To RowToCopy S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("A" & S2Row + (Paul - 1)).PasteSpecial(xlPasteValues) Next Paul End Sub The code does everything I want except to paste the range onto the next available row on worksheet2. It's either all the way up with xlUp or on row 65536 with xlDown. Can you help one more time? "David Heaton" wrote: Paul, The reason on A7 is being copied across is because thats all you are asking it to do. Worksheets("sheet2").Range("A" & S2Row + Paul).Value selects only the cell A & (S2Row+Paul). Which in your case is A7. You need to amend this to be a range identical to the range you are copying from , i.e Worksheets("sheet2").Range("A7:I7").Value would work fine you have a loop running from 1 to RowToCopy, but you've declared RowToCopy to be 1, so essentially your loop is 'For Paul =1 to 1. You may want the option to copy multiple rows later which is why this is here. If not, try this for copying ranges. S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("a" & S2Row).Activate ActiveSheet.Paste hth David "Paul3rd" wrote in message ... Thanks for your reply Don, I'm having a bad day though; where in the code would I insert row+1? "Don Guillett" wrote: row+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul3rd" wrote in message ... Hello, I'm trying to automate a worksheet. I have a command button that calls a module, (Module2), the code selects a range of cells (Range("A7:I7") and copies that range to the next empty row of worksheet2 in the workbook. I can only get cell A7 to copy correctly, none of the remaining Range(B7:I7), copy over. The code is as follows: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range("A7:I7" & Paul).Value Next Paul End Sub I then tried: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("A7:I7") RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range(MyRange).Value Next Paul End Sub But that gives me a RunTime Error #1004 "Application-Defined" or "Object-Defined" error. Thanks in advance for any help, |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
Send me a workbook along with clear instructions of what you want with
before and after examples -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul3rd" wrote in message ... Thanks David, I changed the code to: Sub movedata() Dim S2Row As Long Dim Copies As Integer Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 For Paul = 1 To RowToCopy S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("A" & S2Row + (Paul - 1)).PasteSpecial(xlPasteValues) Next Paul End Sub The code does everything I want except to paste the range onto the next available row on worksheet2. It's either all the way up with xlUp or on row 65536 with xlDown. Can you help one more time? "David Heaton" wrote: Paul, The reason on A7 is being copied across is because thats all you are asking it to do. Worksheets("sheet2").Range("A" & S2Row + Paul).Value selects only the cell A & (S2Row+Paul). Which in your case is A7. You need to amend this to be a range identical to the range you are copying from , i.e Worksheets("sheet2").Range("A7:I7").Value would work fine you have a loop running from 1 to RowToCopy, but you've declared RowToCopy to be 1, so essentially your loop is 'For Paul =1 to 1. You may want the option to copy multiple rows later which is why this is here. If not, try this for copying ranges. S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("a" & S2Row).Activate ActiveSheet.Paste hth David "Paul3rd" wrote in message ... Thanks for your reply Don, I'm having a bad day though; where in the code would I insert row+1? "Don Guillett" wrote: row+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul3rd" wrote in message ... Hello, I'm trying to automate a worksheet. I have a command button that calls a module, (Module2), the code selects a range of cells (Range("A7:I7") and copies that range to the next empty row of worksheet2 in the workbook. I can only get cell A7 to copy correctly, none of the remaining Range(B7:I7), copy over. The code is as follows: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range("A7:I7" & Paul).Value Next Paul End Sub I then tried: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("A7:I7") RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range(MyRange).Value Next Paul End Sub But that gives me a RunTime Error #1004 "Application-Defined" or "Object-Defined" error. Thanks in advance for any help, |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RowToCopy problem
Ok,
to find the next available row in Sheet2 replace S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row with Sheet2.Range("a1").Select Selection.End(xlDown).Select S2Row = Selection.Row + 1 This assumes you have no occasional empty rows (if you do let me know and i'll give you the work around for that) Also I still feel the For...Next loop is redundant . Can you let me know why you need it. Regards David "Paul3rd" wrote in message ... Thanks David, I changed the code to: Sub movedata() Dim S2Row As Long Dim Copies As Integer Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 For Paul = 1 To RowToCopy S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("A" & S2Row + (Paul - 1)).PasteSpecial(xlPasteValues) Next Paul End Sub The code does everything I want except to paste the range onto the next available row on worksheet2. It's either all the way up with xlUp or on row 65536 with xlDown. Can you help one more time? "David Heaton" wrote: Paul, The reason on A7 is being copied across is because thats all you are asking it to do. Worksheets("sheet2").Range("A" & S2Row + Paul).Value selects only the cell A & (S2Row+Paul). Which in your case is A7. You need to amend this to be a range identical to the range you are copying from , i.e Worksheets("sheet2").Range("A7:I7").Value would work fine you have a loop running from 1 to RowToCopy, but you've declared RowToCopy to be 1, so essentially your loop is 'For Paul =1 to 1. You may want the option to copy multiple rows later which is why this is here. If not, try this for copying ranges. S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row Worksheets("sheet1").Range("A7:I7").Select Selection.Copy Worksheets("sheet2").Activate Worksheets("sheet2").Range("a" & S2Row).Activate ActiveSheet.Paste hth David "Paul3rd" wrote in message ... Thanks for your reply Don, I'm having a bad day though; where in the code would I insert row+1? "Don Guillett" wrote: row+1 -- Don Guillett Microsoft MVP Excel SalesAid Software "Paul3rd" wrote in message ... Hello, I'm trying to automate a worksheet. I have a command button that calls a module, (Module2), the code selects a range of cells (Range("A7:I7") and copies that range to the next empty row of worksheet2 in the workbook. I can only get cell A7 to copy correctly, none of the remaining Range(B7:I7), copy over. The code is as follows: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range("A7:I7" & Paul).Value Next Paul End Sub I then tried: Option Explicit Sub movedata() Dim S2Row As Long Dim Paul As Integer Dim RowToCopy As Integer Dim MyRange As Range Set MyRange = ActiveSheet.Range("A7:I7") RowToCopy = 1 S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row For Paul = 1 To RowToCopy Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _ Worksheets("sheet1").Range(MyRange).Value Next Paul End Sub But that gives me a RunTime Error #1004 "Application-Defined" or "Object-Defined" error. Thanks in advance for any help, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |