Go to a new row

V

Vix

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
 
D

Die_Another_Day

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

Charles
 
V

Vix

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 said:
Try something like this:
With Worksheets("Sheet2")
NextFreeCell = .Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

Charles
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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)
 
V

Vix

I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
Bob Phillips said:
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 said:
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
Click to expand...
Click to expand...
 
G

Gary Keramidas

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 said:
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
Bob Phillips said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
V

Vix

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

?????????

Gary Keramidas said:
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 said:
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
Bob Phillips said:
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)

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
Click to expand...
Click to expand...
Click to expand...
 
D

Die_Another_Day

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
I am now experiencing another error, this time with the line
.Cells(iNextRow + 1, "A").Select

?????????

Gary Keramidas said:
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 said:
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
:

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)

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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
I am now experiencing another error, this time with the line
.Cells(iNextRow + 1, "A").Select

?????????

Gary Keramidas said:
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 said:
I am getting a 'Run-time error 1004' on the line starting with Sheets("Entry")
:

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)

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help merging two VBA codes 2
Having difficulty copying and pasting on VBA 3
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
works in 2007 but not in 2010 0
Help again!!! 1
Copying Solver results of each iteration to a given location in a workbook 1
Macro Related to Paste Values 2
Totals An Easier Way?? 5

Top