Excel Copy

  • Thread starter Thread starter timrekdgorf
  • Start date Start date
T

timrekdgorf

i have a bunch of military times that a user is typing in under the W
column starting in cell 9. The user then hits a macro button that will
take all the times they punched in and copy each one 24 times under the
B column starting with cell 9.

for example:
1432 is typed into Cell W9. 1432 gets copied into cell B9-B32.
1526 is typed into Cell W10. 1526 gets copied into cell B33-B56.

Here is my macro code:

Dim copyfield
Dim pastefield1
Dim pastefield2


For counter = 9 To 200

copyfield = "W" & counter
pastefield1 = (counter - 9) * 24 + 9
pastefield2 = pastefield1 + 23
pastefield1 = "B" & pastefield1
pastefield2 = "B" & pastefield2

Worksheets("Sheet1").Range(copyfield).Copy _
Destination:=Worksheets("Sheet1").Range(pastefield1,
pastefield2)
Next counter
End Sub

I keep getting the error "Runtime error 9. Subscript out of range"

Can anyone help me out why?

thanks for your help
-timrekdgorf
 
Works for me

Neil


timrekdgorf said:
i have a bunch of military times that a user is typing in under the W
column starting in cell 9. The user then hits a macro button that will
take all the times they punched in and copy each one 24 times under the
B column starting with cell 9.

for example:
1432 is typed into Cell W9. 1432 gets copied into cell B9-B32.
1526 is typed into Cell W10. 1526 gets copied into cell B33-B56.

Here is my macro code:

Dim copyfield
Dim pastefield1
Dim pastefield2


For counter = 9 To 200

copyfield = "W" & counter
pastefield1 = (counter - 9) * 24 + 9
pastefield2 = pastefield1 + 23
pastefield1 = "B" & pastefield1
pastefield2 = "B" & pastefield2

Worksheets("Sheet1").Range(copyfield).Copy _
Destination:=Worksheets("Sheet1").Range(pastefield1,
pastefield2)
Next counter
End Sub

I keep getting the error "Runtime error 9. Subscript out of range"

Can anyone help me out why?

thanks for your help
-timrekdgorf


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
One way:

Sub CopyTimes()
Const cOffset = 24
Dim vOffset As Integer
Dim Cell As Range
Application.ScreenUpdating = False
' for testing, set some source fields
Range("W9") = 1432
Range("W10") = 1526
Range("W11") = 1933
Range("W12") = 2042
' for each value, copy and autofill down
For Each Cell In Range(Range("W9"), Range("W9").End(xlDown))
Cell.Copy Range("B9").Offset(vOffset, 0)
Range("B9").Offset(vOffset, 0).AutoFill _
Range(Range("B9").Offset(vOffset, 0), _
Range("B9").Offset(vOffset + 23, 0))
vOffset = vOffset + cOffset
Next 'Cell
Application.ScreenUpdating = True
End Sub

Regards

Trevor


timrekdgorf said:
i have a bunch of military times that a user is typing in under the W
column starting in cell 9. The user then hits a macro button that will
take all the times they punched in and copy each one 24 times under the
B column starting with cell 9.

for example:
1432 is typed into Cell W9. 1432 gets copied into cell B9-B32.
1526 is typed into Cell W10. 1526 gets copied into cell B33-B56.

Here is my macro code:

Dim copyfield
Dim pastefield1
Dim pastefield2


For counter = 9 To 200

copyfield = "W" & counter
pastefield1 = (counter - 9) * 24 + 9
pastefield2 = pastefield1 + 23
pastefield1 = "B" & pastefield1
pastefield2 = "B" & pastefield2

Worksheets("Sheet1").Range(copyfield).Copy _
Destination:=Worksheets("Sheet1").Range(pastefield1,
pastefield2)
Next counter
End Sub

I keep getting the error "Runtime error 9. Subscript out of range"

Can anyone help me out why?

thanks for your help
-timrekdgorf


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Actually, it worked for me too. The version I posted might be a bit quicker
given that it only copies the number of cells entered rather than just
looping between 9 and 200. Of course , if all those cells are filled it
won't make a whole lot of difference.

Sub test()
Dim copyfield
Dim pastefield1
Dim pastefield2
Dim counter

Range("W9") = 1432
Range("W10") = 1526
Range("W11") = 1933
Range("W12") = 2042

For counter = 9 To 200

copyfield = "W" & counter
pastefield1 = (counter - 9) * 24 + 9
pastefield2 = pastefield1 + 23
pastefield1 = "B" & pastefield1
pastefield2 = "B" & pastefield2

Worksheets("Sheet1").Range(copyfield).Copy _
Destination:=Worksheets("Sheet1").Range(pastefield1, pastefield2)
Next counter
End Sub

Regards

Trevor
 
Expanding on your excellent idea might be the use of "Resize."

Sub Demo()
Dim Cell As Range

' Small test data
[W9:W12] = [Transpose({1432,1526,1933,2042})]

Application.ScreenUpdating = False

For Each Cell In Range([W9], [W9].End(xlDown))
Cells(24 * Cell.Row - 207, 2).Resize(24, 1) = Cell
Next

Application.ScreenUpdating = True
End Sub
 
Back
Top