Paste into next blank row in a different worksheet

[dhunter43]

I need to copy a row of data from worksheet1 to the next available row in
worksheet2. Worksheet1 cells (A2:K2) display results from a 'LOOKUP' function
and cells (L2:AB2) in worksheet1 utilize the 'IF' function to display
results from worksheet3. I only want the results from the LOOKUP and IF
functions to populate the next available row in worksheet2.
I'd like to utilize the 'CommandButton' that I've programed witha macro to
clear the contents of worksheet3 to initialize or complete the transfer of
data from worksheet1 to worksheet2. Or will that happen automatically when I
clear the data from worksheet3?

 

[Joel]

You have to clear sheet3. Hre is some code that may help

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/25/2007
'

'
Sheets("Sheet1").Select
Rows("20:20").Select
Selection.Copy
Sheets("Sheet2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet3").Range("B5").EntireRow.ClearContents

End Sub

 

[dhunter43]

I am receiving a "Compile Error: Syntax error" when I try to run this macro.
The hilighted copy is
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,

I associated the macro with the 'CommandButton' I placed in worksheet3, is
that the right place for it?
I appreciate your assistance. I need to log off in a few minutes to get to
work but will look for your response when I get home tonight.
Thank you,

 

[Joel]

The proble has tto due with line wrapping. When code is pasted on this
website the length is too long and then when it is pasted in VBA it gives an
error

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/25/2007
'

'
Sheets("Sheet1").Select
Rows("20:20").Select
Selection.Copy
Sheets("Sheet2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet3").Range("B5").EntireRow.ClearContents

End Sub

 

[dhunter43]

For some reason this line of code only copies to row19 even though there are
blank rows above it. It would be ok if it pasted data to row20 next and so on
but all data is pasted to row19 over and over again.

LastRow = Cells(Rows.Count, "a").End(xlUp).Row + 1
ActiveSheet.Rows(LastRow & ":" & LastRow).Select

I cannot figure out where the problem is.
Thank you

 

[Joel]

Try this. the code was using the selected range on sheet one for pasting the
cells on sheett 2.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/25/2007
'

'
Sheets("Sheet1").Rows("20:20").Select
Selection.Copy
LastRow = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet3").Range("B5").EntireRow.ClearContents

End Sub

 

[dhunter43]

results are no different.

 

[Joel]

I tested this and it works fine. Changge the activation of the worksheets

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/25/2007
'

'
Sheets("Sheet1").Activate
Rows("20:20").Select
Selection.Copy
Sheets("Sheet2").Activate
LastRow = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Sheet3").Range("B5").EntireRow.ClearContents

End Sub

 

[dhunter43]

Using some input from Excel help I added some text to a couple of lines. The
following code is working.

Sheets("Formulas").Activate
Sheets("formulas").Rows(2).Select
Selection.Copy
Sheets("OperationalRates").Activate
LastRow = Sheets("OperationalRates"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("OperationalRates").Rows(LastRow & ":" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("InputForm").Range("c2:c10").ClearContents

I appreciate your assistance and even moreso your patience. I've learned a
great deal from you and this experience. The more I've learned the more I
want to know.

Thank You!!!!

 

[Les Stout]

Hi dhunter43,

just your code reduced slightly:

Sub test()
'
Dim lastRow As Long
lastRow = Sheets("OperationalRates"). _
Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Formulas").Activate
Sheets("formulas").Rows(2).Copy
Sheets("OperationalRates").Rows(lastRow & ":" & lastRow).PasteSpecial
Paste _
:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
Sheets("InputForm").Range("C2:C10").ClearContents
End Sub


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[dhunter43]

Thank you, I will rename the workbook and try this code. I appreciate all the
help I"ve been getting and am always looking for opportunities to learn.