如何从其他Excel文件随机获取数据并保留单元格颜色？

Hey there, I've got you covered on this Excel task. You need to pull random data from Excel1.xlsx into Excel2.xlsx while keeping the cell fill colors intact—built-in Excel functions can't handle formatting like fill colors natively, so we'll use VBA to make this work smoothly.

1. Pre-Requisites #

• Make sure both Excel1.xlsx and Excel2.xlsx are open in Excel
• Confirm your target source cells are B1, C1, D1 in Sheet1 of Excel1.xlsx

2. Write the VBA Code #

Open Excel2.xlsx, press Alt + F11 to launch the VBA Editor, then insert a new module (right-click your workbook in the Project Explorer > Insert > Module). Paste this code:

Sub GetRandomColoredCell()
    ' Define variables for workbooks, sheets, and cells
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    Dim sourceCells As Range
    Dim randomCell As Range
    
    ' Link to the source workbook and sheet
    Set sourceWB = Workbooks("Excel1.xlsx")
    Set sourceWS = sourceWB.Sheets("Sheet1")
    ' Set your target sheet in Excel2 (adjust the sheet name if needed)
    Set targetWS = ThisWorkbook.Sheets("Sheet1")
    
    ' Specify the range of cells to pick from randomly
    Set sourceCells = sourceWS.Range("B1:D1")
    
    ' Generate a random index to select one cell from the range
    Set randomCell = sourceCells.Cells(Int(Rnd * sourceCells.Cells.Count) + 1)
    
    ' Copy both the cell's value and its fill color to the target cell
    randomCell.Copy
    targetWS.Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    targetWS.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    ' Clear the clipboard to avoid leftover copy data
    Application.CutCopyMode = False
End Sub

3. Code Breakdown #

• Rnd generates a random number between 0 and 1; we use it to pick a random cell from your source range
• xlPasteAllUsingSourceTheme ensures the fill color (and other formatting) is copied exactly
• xlPasteValuesAndNumberFormats guarantees the cell's value and number formatting match the source
• The final line clears the clipboard so you don't accidentally paste the copied cell elsewhere

4. Run the Code #

• Switch back to Excel, press Alt + F8, select GetRandomColoredCell from the list, then click "Run"
• Each run will randomly select one of B1/C1/D1 from Excel1, and paste its value + fill color into cell A1 of Excel2 (adjust the target cell in the code if you need it elsewhere)

Quick Adjustments #

• If Excel1.xlsx isn't open when you run the code, replace Set sourceWB = Workbooks("Excel1.xlsx") with Set sourceWB = Workbooks.Open("C:\Your\Full\Path\To\Excel1.xlsx")
• Change targetWS.Range("A1") to any cell/range in Excel2 where you want the random data to go

内容的提问来源于stack exchange，提问作者hieu