+ Reply to Thread
Results 1 to 2 of 2

Cell Selection based on user click

Hybrid View

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Cell Selection based on user click

    Hi There

    I am trying to copy a selection of cells(which may vary from time to time)
    To a folder location specifed in a cell on the same worksheet.
    It works if I specify which cell contents I would like to copy, but not as a selection

    I would like the program to know which cell contents are selected and where it should be copied to, based on user selection in the workbook.
    Please try to assist if possible




    Sub Copy_Certain_Files_In_Folder()


    'Declaration
    Dim FSO As FileSystemObject
    Dim sFile As String, rng
    Dim sSFolder As String
    Dim sDFolder As String
    Set rng = Selection


    Sheets("CONTROL").Activate

    'This is Your File Name which you want to Copy.You can change File name at B5.
    'sFile = Sheets("CONTROL").Range("G8").Value
    sFile = Sheets("CONTROL").Range("G8:G500").Resize(rng.Rows.count, _
    rng.Columns.count).Value = rng.Value

    'sFile = Sheets("CONTROL").Range(Count_Selection)


    'Change to match the source folder path. You can change Source Folder name at B6.
    sSFolder = Sheets("CONTROL").Range("G7")

    'Change to match the destination folder path. You can change Destination Folder name at B6.
    sDFolder = Sheets("CONTROL").Range("H7")

    'Create Object for File System
    Set FSO = CreateObject("Scripting.FileSystemObject")

    'Checking If File Is Located in the Source Folder
    If Not FSO.FileExists(sSFolder & "\" & sFile) Then
    MsgBox "Specified File(sFile) Not Found in Source Folder", vbInformation, "Not Found"

    'Copying If the Same File is Not Located in the Destination Folder
    ElseIf Not FSO.FileExists(sDFolder & sFile) Then
    FSO.CopyFile (sSFolder & "\" & sFile), sDFolder & "\", True

    MsgBox "Specified File Copied to Destination Folder Successfully", vbInformation, "Done!"
    Else
    MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
    End If

    End Sub
    Sub Count_Selection()
    Dim cell As Object
    Dim count As Integer
    count = 0
    For Each cell In Selection
    count = count + 1
    Next cell
    MsgBox count & " item(s) selected"
    End Sub

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: Cell Selection based on user click

    Pls use the code tag around your code, you can highlight the code part of your post and then hit the # symbol above the box you type in...
    I'm afraid i still don't quite understand the problem.. is the code you posted the non-working code? the ammended code with static cells...
    I assume the rng = selection line works? I normally would dim rng as range rather than throw it in after sFile, but that's just me. Is the count_selection module relevant or just pasted in by accident? If you are using it, you can get the same result in your first code by doing count = rng.cells.count

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional formatting changes based on user selection
    By jrholden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2013, 05:07 PM
  2. Conditional formatting changes based on user selection
    By jrholden in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2013, 01:09 PM
  3. Replies: 2
    Last Post: 07-01-2013, 10:51 AM
  4. Replies: 1
    Last Post: 06-03-2013, 06:30 AM
  5. Open file based on user selection
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2008, 11:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1