i have a macro that copies data from another sheet, the problem i have is
when ever i use the macro it puts the data into the same place every time
What i would like is the macro to start from the next available blank cell
in cloumn A

My Simple macro looks like below
Is there some code i need to add to the front


Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Pilot Valve Material").Select
Range("A2:H50").Select
Selection.Copy
Sheets("Stock Transactions").Select
Range("A1597").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=48
Range("A1645").Select
Application.CutCopyMode = False
Application.Run "'Test Macro - Stock Transactions.xlsm'!Macro2"
ActiveWindow.SmallScroll Down:=-42
Range("A1597").Select
Application.Goto Reference:="Macro2"
End Sub

Shane,

These couple of lines do all the copying and pasting into the first empty
row of the sheet

lastrow = Sheets("Stock Transactions").Cells(Cells.Rows.Count,
"A").End(xlUp).Row
Sheets("Pilot Valve Material").Range("A2:H50").Copy _
Destination:=Sheets("Stock Transactions").Range("A" & lastrow + 1)

Miike

"Shane" wrote:

i have a macro that copies data from another sheet, the problem i have is
when ever i use the macro it puts the data into the same place every time
What i would like is the macro to start from the next available blank cell
in cloumn A

My Simple macro looks like below
Is there some code i need to add to the front


Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Pilot Valve Material").Select
Range("A2:H50").Select
Selection.Copy
Sheets("Stock Transactions").Select
Range("A1597").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=48
Range("A1645").Select
Application.CutCopyMode = False
Application.Run "'Test Macro - Stock Transactions.xlsm'!Macro2"
ActiveWindow.SmallScroll Down:=-42
Range("A1597").Select
Application.Goto Reference:="Macro2"
End Sub

try this idea

sub copytonextavailrowwithoutselections()
with Sheets("Stock Transactions")
nar=.cells(rows.count,1).end(xlup).row+1
Sheets("Pilot Valve Material").Range("A2:H50").copy .cells(nar,1)
end with
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shane" wrote in message
...
i have a macro that copies data from another sheet, the problem i have is
when ever i use the macro it puts the data into the same place every time
What i would like is the macro to start from the next available blank cell
in cloumn A

My Simple macro looks like below
Is there some code i need to add to the front


Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Pilot Valve Material").Select
Range("A2:H50").Select
Selection.Copy
Sheets("Stock Transactions").Select
Range("A1597").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=48
Range("A1645").Select
Application.CutCopyMode = False
Application.Run "'Test Macro - Stock Transactions.xlsm'!Macro2"
ActiveWindow.SmallScroll Down:=-42
Range("A1597").Select
Application.Goto Reference:="Macro2"
End Sub

Mike thank you for the reply

I know I'm being thick but where do i add the data

If i was to completly delete what was in the macro how should it end up
looking

Shane

"Mike H" wrote:

Shane,

These couple of lines do all the copying and pasting into the first empty
row of the sheet

lastrow = Sheets("Stock Transactions").Cells(Cells.Rows.Count,
"A").End(xlUp).Row
Sheets("Pilot Valve Material").Range("A2:H50").Copy _
Destination:=Sheets("Stock Transactions").Range("A" & lastrow + 1)

Miike

"Shane" wrote:

i have a macro that copies data from another sheet, the problem i have is
when ever i use the macro it puts the data into the same place every time
What i would like is the macro to start from the next available blank cell
in cloumn A

My Simple macro looks like below
Is there some code i need to add to the front


Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Pilot Valve Material").Select
Range("A2:H50").Select
Selection.Copy
Sheets("Stock Transactions").Select
Range("A1597").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=48
Range("A1645").Select
Application.CutCopyMode = False
Application.Run "'Test Macro - Stock Transactions.xlsm'!Macro2"
ActiveWindow.SmallScroll Down:=-42
Range("A1597").Select
Application.Goto Reference:="Macro2"
End Sub