Hi Hilary,
Below code will help you
Option Explicit
Sub Copy_Visited_Client_Data()
Dim wksModule As Worksheet
Dim wksDashBoard As Worksheet
Dim wksUserSht As Worksheet
Dim rngDataRange As Range
Dim rngDstRange As Range
Dim objDropDown As DropDown
Dim varData() As Variant
Dim lngCount As Long
Const strDataStartCell As String = "C2"
Const strDestinationCell As String = "J1"
Const strUserControlListName As String = "Drop Down 2"
With ThisWorkbook
Set wksModule = .Worksheets("Module")
Set wksDashBoard = .Worksheets("Dashboard")
End With
With wksDashBoard
Set objDropDown = .DropDowns(strUserControlListName)
End With
With ThisWorkbook
Set wksUserSht = Nothing
On Error Resume Next
Set wksUserSht =
.Worksheets(objDropDown.List(objDropDown.ListIndex))
On Error GoTo -1: On Error GoTo 0: Err.Clear
End With
If Not wksUserSht Is Nothing Then
With wksUserSht
Set rngDataRange = Nothing
On Error Resume Next
Set rngDataRange = .Range(strDataStartCell)
Set rngDataRange = rngDataRange.Resize(.Cells(.Rows.Count,
rngDataRange.Column).End(xlUp).Row, 1)
If rngDataRange.Rows.Count > 1 Then
Set rngDataRange = Intersect(rngDataRange,
rngDataRange.Offset(1))
Else
Set rngDataRange = Nothing
End If
On Error GoTo -1: On Error GoTo 0: Err.Clear
End With
If Not rngDataRange Is Nothing Then
If rngDataRange.Rows.Count > 0 Then
lngCount = 0
Erase varData
For Each rngDstRange In rngDataRange
If rngDstRange.Value <> "" Then
lngCount = lngCount + 1
ReDim Preserve varData(1 To lngCount)
varData(lngCount) = rngDstRange.Value
End If
Next rngDstRange
With wksModule
Set rngDstRange = .Range(strDestinationCell)
Set rngDstRange =
rngDstRange.Resize(.Cells(.Rows.Count, rngDstRange.Column).End(xlUp).Row, 1)
With rngDstRange
If .Rows.Count > 1 Then
.Offset(1).ClearContents
End If
If lngCount > 0 Then
.Offset(1).Resize(UBound(varData)).Value =
Application.Transpose(varData)
End If
End With
End With
End If
End If
End If
Set wksModule = Nothing
Set wksDashBoard = Nothing
Set wksUserSht = Nothing
Set rngDataRange = Nothing
Set rngDstRange = Nothing
Set objDropDown = Nothing
Erase varData
lngCount = Empty
End Sub
On Thursday, 28 March 2013 17:41:33 UTC+5:30, hilary lomotey wrote:
>
> Hi Experts
>
> i have attempted to write a VBA code to extract data from one sheet to
> another but am getting a debug error, kindly assist. What am trying to
> achieve is
> if i select a name from the list of users in the dashboard, that name is
> always a sheet name, i want to copy all the list of institution visited
> from the selected sheet name (or member of the user list) and paste in the
> module sheet range J2, but this has to be automatic ie when i select a user
> it shd run the code and paste it in that range. thanks
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
