Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Go to a new row
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Vix Vix is offline
external usenet poster
  
Posts: 9
Default Go to a new row
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 644
Default Go to a new row
Try something like this:
With Worksheets("Sheet2")
NextFreeCell = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

Charles

Vix wrote:
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Vix Vix is offline
external usenet poster
  
Posts: 9
Default Go to a new row
Thanks for your response.

However, this code just overwrites the first row again. I have 31 rows on
sheet2 and need each row to have overtime data transferred from the first
sheet whenever the macro is run (which will be most days).

I put this code at the end of the code, before the line 'End Sub'

Any ideas?

"Die_Another_Day" wrote:

Try something like this:
With Worksheets("Sheet2")
NextFreeCell = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

Charles

Vix wrote:
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Go to a new row
Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Vix Vix is offline
external usenet poster
  
Posts: 9
Default Go to a new row
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
"Bob Phillips" wrote:

Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Go to a new row
not sure exactly about your set up, but bob defines iLastRow but uses iNextRow

try changing
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
to
iNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1


--


Gary


"Vix" wrote in message
...
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
"Bob Phillips" wrote:

Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
Vix Vix is offline
external usenet poster
  
Posts: 9
Default Go to a new row
I am now experiencing another error, this time with the line
..Cells(iNextRow + 1, "A").Select

?????????

"Gary Keramidas" wrote:

not sure exactly about your set up, but bob defines iLastRow but uses iNextRow

try changing
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
to
iNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1


--


Gary


"Vix" wrote in message
...
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
"Bob Phillips" wrote:

Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub







Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 644
Default Go to a new row
In order to select a cell, the sheet in which it resides must be the
ActiveSheet. Try Sheets("Form").Select
Why do you want to select the cell? Selecting cells slows down the
macro.

Charles

Vix wrote:
I am now experiencing another error, this time with the line
.Cells(iNextRow + 1, "A").Select

?????????

"Gary Keramidas" wrote:

not sure exactly about your set up, but bob defines iLastRow but uses iNextRow

try changing
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
to
iNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1


--


Gary


"Vix" wrote in message
...
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
"Bob Phillips" wrote:

Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 = "=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Go to a new row
This compiles ok

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow, "D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 = _
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 = _
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow, "F").Resize(,
3)
.Activate
.Cells(iNextRow + 1, "A").Select
End With
End Sub



--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I am now experiencing another error, this time with the line
.Cells(iNextRow + 1, "A").Select

?????????

"Gary Keramidas" wrote:

not sure exactly about your set up, but bob defines iLastRow but uses
iNextRow

try changing
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
to
iNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1


--


Gary


"Vix" wrote in message
...
I am getting a 'Run-time error 1004' on the line starting with
Sheets("Entry")
"Bob Phillips" wrote:

Is this what you want

Sub Overtime()
Dim iNextRow As Long

With Sheets("Form")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Entry").Range("D4").Copy .Cells(iNextRow, "A")
Sheets("Entry").Range("D6:D8").Copy .Cells(iNextRow,
"D").Resize(,
3)
.Cells(iNextRow, "C").FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
.Cells(iNextRow, "D").FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
.Cells(iNextRow, "E").FormulaR1C1 = "=RC[-1]-RC[-2]"
.Cells(iNextRow, "E").NumberFormat = "h.mm"
Sheets("Entry").Range("D10:D12").Copy .Cells(iNextRow,
"F").Resize(,
3)
.Cells(iNextRow + 1, "A").Select
End With
End Sub

--
HTH

Bob Phillips

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

"Vix" wrote in message
...
I have a workbook containing two sheets.
On sheet1 named 'Entry', all the details of overtime are entered.

When the details of that days overtime are entered, the macro is
run which
transfers the info into row3 of sheet2, which is named 'Form'

This works fine, apart from the next time any overtime info is
added and
the
macro is run, the info goes into row3 again.
I am not sure how to go down into the next row??

The code is shown below.

Sub Overtime()
Sheets("Entry").Select
Range("D4").Select
Selection.Copy
Range("C23").Select
Sheets("Form").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A10:A13").Select
Sheets("Entry").Select
Range("D6:D8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Form").Select
Range("B4:D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("C4:D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "5:30:00 PM"
Range("C4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[3]C[1])/""0:15""),0)*""0:15"""
Range("D4").Select
ActiveCell.FormulaR1C1 =
"=ROUND(((Entry!R[4]C)/""0:15""),0)*""0:15"""
Range("E4").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E4").Select
Selection.NumberFormat = "h.mm"
Sheets("Entry").Select
Range("D10:D12").Select
Selection.Copy
Sheets("Form").Select
Range("F4:H4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A5").Select
End Sub









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 10:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement