Need Help with a MACRO

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Obi-Wan Kenobi - 20 Mar 2006 18:32 GMT

I need help with a macro. On my workbook I have two Worksheets.

Sheet1 is were I keep the form. Sheet2 is were the data is transferre
when I run the macro. The macro I have built works fine in transferrin
the data from the form (Sheet1) to the table on sheet2. I also have
clear button on sheet1 which when pressed the macro clears all data o
the form (Sheet1), so it is ready to input more data.

My problem starts from here, when I input new data on the form and ru
the macro in order to transfer data across to the table on sheet2. Th
previous data is overwritten and I can not build a list of customers
Is there a way of building a macro that will select the NEXT LINE o
the table every time I run the transfer button?:eek

--
Obi-Wan Kenob

Reply to this Message

Otto Moehrbach - 20 Mar 2006 18:44 GMT

You didn't include the code you are using so I have to give a generic
answer. In your code, you are copying and pasting. Your problem is
designating where to paste. I like to use a variable range that is the
first empty cell in some column, then offset from that cell for repeated
pasting.
You would use something like this line of code to set the destination cell.
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
HTH Otto

> I need help with a macro. On my workbook I have two Worksheets.
>
[quoted text clipped - 9 lines]
> Is there a way of building a macro that will select the NEXT LINE on
> the table every time I run the transfer button?:eek:

Reply to this Message

Obi-Wan Kenobi - 20 Mar 2006 19:00 GMT

Sorry for not including the macro, here it is can you help

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 20/03/2006 by s
'

'
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Su

--
Obi-Wan Kenob

Reply to this Message

Tom Ogilvy - 20 Mar 2006 19:32 GMT

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 20/03/2006 by s
'

'
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

should be replaced with
Sub transfer1()
Dim rng as Range
set rng = worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)
worksheets("Sheet1").Range("A2:C2").copy rng
End sub

Signature

Regards,
Tom Ogilvy

> Sorry for not including the macro, here it is can you help
>
[quoted text clipped - 28 lines]
> Application.CutCopyMode = False
> End Sub

Reply to this Message

Obi-Wan Kenobi - 20 Mar 2006 19:46 GMT

Thanks everyone looks like the problem, is sorted for now. That bein
said Im full of problems so no doubt we will be talking again

--
Obi-Wan Kenob

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:57 GMT

with worksheets("sheet2")
set rng = .cells(rows.count,"A").end(up)(2)
End with

now use rng to determine where to write the data. It points to the next
empty cell in column 1 of sheet2. rng(1,2) is column B, rng(1,3) is
column C.

Signature

Regards,
Tom Ogilvy

Reply to this Message