Autofil random cell in another worksheet [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,
Please help me write the code for below situation:
1. I have 2 sheet "Merger_Pole" and "Config".
2. In Merger_Pole, I set F2=Config!C29 (randomly, may be Config!C30 or another).
3. I want to autofill F3:F128 with random cell in Config (may be Config!C14 or not).
Thanks and Best regards.


1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Ptqc,

I'm not sure if I fully understand your query but I assume that you would like to randomly select a cell from Column C in the "Config" sheet and automatically transfer the cell value to Column F in the "Merger_Pole" sheet. If I have assumed correctly, then the following code may work for you:-


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 3 And Target.Cells.Count = 1 Then
      Target.Copy Sheet1.Range("F" & Rows.Count).End(3)(2)
End If

Application.CutCopyMode = False
Application.EnableEvents = True

Sheet1.Select

End Sub


The code is a Worksheet_SelectionChange event so as soon as you click on a cell in Column C of the "Config" sheet, the cell value will be transferred to Column F in the "Merger_Pole" sheet.

Following is the link to a little sample that I have prepared for you:-

https://www.dropbox.com/s/xq5ah0zpo4z33ny/Ptqc%28Transfer%20randomly%20selected%20cells%20to%20another%20sheet%29.xlsm?dl=0

In the "Config" sheet, select any cell in Column C to see its value transferred to the "Merger_Pole" sheet.

To implement the code into your workbook, right click on the "Config" sheet tab and select "View Code" from the menu that appears. In the big white field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.