Select Range From Last Cell

[Zanatos1986]

I have code that uses two drop down lists to determine which sheet to access in my workbook. These sheets have data that only exist on row A, From A1 to A##
I need to determine the last column being used to get my range (i.e. A1:A34), select and copy this range to past back on Sheet1

I have found numerous code to find the last column but I can never seem to select the cells I want. Any help would be great!



Private Sub Worksheet_Change(ByVal Target As Range)

' Select the XXX
If Target.Address = "$E$10" Then
Select Case Target.Value
Case "XXX"
Call Macro1

Case "XX"
Call Macro2

Case ""
Call Macro3
End Select
End If

' Select The XXXX
If Target.Address = "$E$12" Then
'MsgBox Target.Value
Sheets(Target.Value).Select


Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Range("A" & lRow)).Select


End If

End Sub

 

[wigi]

Maybe:

With ThisWorkbook.Sheets(Target.Value)
   lRow = .Range("A" & .Rows.Count).End(xlUp).Row
   .Range("A1:A" & lRow).Copy ... 'destination of the copy action
End With

PS: Please use code tags when you paste VBA code on the forum.
To add code tags, you should type in your post:

then paste your code, and lastly, type:

['/code]

WITHOUT the ' in front of the /

Thanks for the consideration.

 

[Zanatos1986]

This worked for selecting my cell range perfectly, but I am having a difficult time getting the data to go where I want.

Once I have selected this data range I want it to go back to Sheet1 and auto populate into a ActiveX TextBox with Scrollbar. I have the scroll box setup, but I am not sure how to make the data populate into it. I was thinking to take my data range and creating a name for it (since it is ever changing) and have my textbox reference that, but I'm not sure if that would work. I am very new to ActiveX controls.

I have tried a couple of things to move my data into the TextBox but I don't know how other than copy and paste, and when I do that and record a macro I can't replicate what I did...

' Select The XXX
If Target.Address = "$E$12" Then
   'MsgBox Target.Value
   Sheets(Target.Value).Select
   
   
Dim lRow As Long
With ThisWorkbook.Sheets(Target.Value)
   lRow = .Range("A" & .Rows.Count).End(xlUp).Row
   .Range("A1:A" & lRow).Select
End With
   
   
End If