Excel VBA Newbie

Hello,
I am just learning excel VBA so all suggestions are greatly appreciated.

Goal...
User selects a range of cells from the active sheet (any range not "A1:E1"
as below).
The selected range is then pasted on the "Red" sheet in the next available
empty row (no A1 as below).

I have recorded the macro below with the hope of modifying it to accomplish
the above. I have stumped myself before getting out of the starting blocks.

Sub Macro2()

Range("A1:E1").Select
Selection.Copy
Sheets("Red").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
--
Thank-you and all suggestions are appreciated.

Try this code...

Public Sub CopyLine()
Dim wksFrom As Worksheet
Dim wksTo As Worksheet
Dim rngFrom As Range

Set wksFrom = ActiveSheet
Set rngFrom = wksFrom.Range("A1:E1")
Set wksTo = Sheets("Red")

rngFrom.Copy wksTo.Range("A65535").End(xlUp).Offset(1, 0)

End Sub

"Anauna" wrote:

> Hello,
> I am just learning excel VBA so all suggestions are greatly appreciated.
>
> Goal...
> User selects a range of cells from the active sheet (any range not "A1:E1"
> as below).
> The selected range is then pasted on the "Red" sheet in the next available
> empty row (no A1 as below).
>
> I have recorded the macro below with the hope of modifying it to accomplish
> the above. I have stumped myself before getting out of the starting blocks.
>
> Sub Macro2()
>
> Range("A1:E1").Select
> Selection.Copy
> Sheets("Red").Select
> Range("A1").Select
> ActiveSheet.Paste
> End Sub
> --
> Thank-you and all suggestions are appreciated.

Try:

Sub Test()
Selection.Copy
Sheets("Red").Select
Range("A65536").End(xlUp).Offset(1,0).Select
ActiveSheet.Paste
End Sub

Regards

Trevor


"Anauna" <Anauna@discussions.microsoft.com> wrote in message
news:52E79575-3221-4879-BCB1-E15124623AE1@microsoft.com...
> Hello,
> I am just learning excel VBA so all suggestions are greatly appreciated.
>
> Goal...
> User selects a range of cells from the active sheet (any range not "A1:E1"
> as below).
> The selected range is then pasted on the "Red" sheet in the next available
> empty row (no A1 as below).
>
> I have recorded the macro below with the hope of modifying it to
> accomplish
> the above. I have stumped myself before getting out of the starting
> blocks.
>
> Sub Macro2()
>
> Range("A1:E1").Select
> Selection.Copy
> Sheets("Red").Select
> Range("A1").Select
> ActiveSheet.Paste
> End Sub
> --
> Thank-you and all suggestions are appreciated.

One way:

ActiveSheet.Range("A1:E1").Copy _
Destination:=Sheets("Red").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)



Note that selecting or activating is almost never necessary (though
that's what the macro recorder uses).

In article <52E79575-3221-4879-BCB1-E15124623AE1@microsoft.com>,
Anauna <Anauna@discussions.microsoft.com> wrote:

> Hello,
> I am just learning excel VBA so all suggestions are greatly appreciated.
>
> Goal...
> User selects a range of cells from the active sheet (any range not "A1:E1"
> as below).
> The selected range is then pasted on the "Red" sheet in the next available
> empty row (no A1 as below).
>
> I have recorded the macro below with the hope of modifying it to accomplish
> the above. I have stumped myself before getting out of the starting blocks.
>
> Sub Macro2()
>
> Range("A1:E1").Select
> Selection.Copy
> Sheets("Red").Select
> Range("A1").Select
> ActiveSheet.Paste
> End Sub