title: Excel columns of several worksheets - copy, sort, hyperlink
body: <p>Need some help with the following:</p>
  
  <p>I have several worksheets with the same structure and within each worksheet I have two columns (let's call them X &amp; Y) that I need to copy with their cellvalues (letter-number combination) and also copy the values of Column A-F to an own sheet for X and Y. </p>
  
  <p>On the "new" sheet I want to put X/Y to column A sort the values after A and attach a constant hyperlink to each cellvalue in A.
  So X or Y goes to A and A-F to B-G.</p>
  
  <p>Then I want to make column F or the new G clickable so that it will take me to the row in the according worksheet.
  X and Y don't always happen to be in column X or Y but I think this can be solved with a "name search".</p>
  
  <p>When I execute my code then for example worksheet3 will overwrite the values of worksheet1 and my hyperlink structure is wrong too. The sorting is left out since that is working.</p>
  
  <pre><code>Function CopyAndSort(ByRef mySheet As Worksheet)
  '   If mySheet.Name &lt;&gt; "Sheet1" Then
  '   Exit Function
  '   End If
  
     mySheet.Activate
      Set sheetCS = Sheets("CopyAndSort Sheet")
      sheetCS.Range("A:A").Value = ""
     lastRowCS = Range("X:X").Cells.Find("*", , , , , xlPrevious).Row
  
       rowNumber = 1
      For rowCopy = 5 To lastRowFO
          sheetCopy = Range("BE" &amp; rowCopy)
          If Trim(sheetCopy) &lt;&gt; "" Then
              sheetCopy = Replace(sheetCopy, """", "")
              If InStr(1, sheetCopy, ",", vbTextCompare) &lt;&gt; 0 Then
                 sheetCopyArray = Split(sheetCopy, ",")
              Else
                 sheetCopyArray = Array(sheetCopy)
        End If
  
              For Each copy In sheetCopyArray
  
                  rowNumber = rowNumber + 1
  
                          copy_Value = copy
  ' test for url                         
  '  sheetCS.Cells(rowNumber, 1).Formula = "=HYPERLINK(""ConstURL &amp; copyValue"")"
  
                       sheetCS.Cells(rowNumber, 1) = copy_Value
                          copy_Value = Cells(rowCopy, 1)
                              sheetCS.Cells(rowNumber, 2) = copy_Value
                          copy_Value = Cells(rowCopy, 2)
                              sheetCS.Cells(rowNumber, 3) = copy_Value
                          copy_Value = Cells(rowCopy, 3)
                              sheetCS.Cells(rowNumber, 4) = copy_Value
                              copy_Value = Cells(rowCopy, 4)
                              sheetCS.Cells(rowNumber, 5) = copy_Value
                          copy_Value = Cells(rowCopy, 5)
                              sheetCS.Cells(rowNumber, 6) = copy_Value
  
              Next
          End If
  
      Next 
  </code></pre>
  
  <p>So how can I manage to not overwrite the values and attach the correct hyperlink syntax, plus making colum G clickable?
  And can I use one function for X and Y?
  Some code examples would help me alot. 
  Thank you.</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>i forgot to mention that X &amp; Y will always be next to each other.</p>
  
  <p>Example:</p>
  
  <p>Sheet1: </p>
  
  <pre><code>|ColA|ColB|ColC|ColD|ColF|....|ColX|ColY|
  </code></pre>
  
  <p>Sheet2: here "ColX" is in ColQ and ColY in ColR</p>
  
  <pre><code>|ColA|ColB|ColC|ColD|ColF|....|ColXinColQ|ColYinColR|
  </code></pre>
  
  <p>CopySheet_of_X: now copy ColX plus ColA-ColF of Sheet1 and do the same for Sheet2 where X is in ColQ</p>
  
  <p><strong>Output for both sheets:</strong>
      |ColX|ColA|ColB|ColC|ColD|ColF|</p>
  
  <p>CopySheet_of_Y: now copy ColY plus ColA-ColF of Sheet1 and do the same for Sheet2 where Y is in ColR</p>
  
  <p><strong>Output for both sheets:</strong>
      |ColY|ColA|ColB|ColC|ColD|ColF|</p>
  
  <p>Hyperlink:
  so now the values of ColX and ColY should be concatenated with a preceding hyperlink:
  If a cell in ColX has the value of "someValue1" then it should be turned into myurl://sometext=someValue1</p>
  
  <p>and I don't know the right way to jump back to the row when clicking on ColF.</p>