基于Cleaned工作表H25单元格设置VBA宏中Weekly formula列的问题

Hey there! Since you're new to VBA, let's break this down simply. The goal is to swap out all those hardcoded "C" references in your macro with the column letter stored in Cleaned!H25. Here's how to do it step by step:

Step 1: Add a variable to store the dynamic column letter #

First, we'll create a string variable to hold the column letter from Cleaned!H25. This makes it easy to reuse throughout the macro instead of repeating the cell reference every time.

Step 2: Update all range references to use the dynamic column #

Instead of writing "C6", we'll combine the column variable with the row number using & (VBA's concatenation operator) to build a valid range string.

Here's your modified macro with these changes:

Sub Copy_Data() 
    Application.ScreenUpdating = False 
    
    ' Get the target column letter from Cleaned!H25
    Dim targetColumn As String
    targetColumn = Worksheets("Cleaned").Range("H25").Value
    
    ' Use the dynamic column instead of fixed "C"
    Worksheets("Weekly formula").Range(targetColumn & "6").Value = Worksheets("Cleaned").Range("b55").Value 
    Worksheets("Weekly formula").Range(targetColumn & "10").Value = Worksheets("Cleaned").Range("c55").Value 
    Worksheets("Weekly formula").Range(targetColumn & "8").Value = Worksheets("Cleaned").Range("d55").Value 
    Worksheets("Weekly formula").Range(targetColumn & "35").Value = Worksheets("Cleaned").Range("b51").Value 
    Worksheets("Weekly formula").Range(targetColumn & "39").Value = Worksheets("Cleaned").Range("c51").Value 
    Worksheets("Weekly formula").Range(targetColumn & "37").Value = Worksheets("Cleaned").Range("d51").Value 
    Worksheets("Weekly formula").Range(targetColumn & "64").Value = Worksheets("Cleaned").Range("b50").Value 
    Worksheets("Weekly formula").Range(targetColumn & "68").Value = Worksheets("Cleaned").Range("c50").Value 
    Worksheets("Weekly formula").Range(targetColumn & "66").Value = Worksheets("Cleaned").Range("d50").Value 
    Worksheets("Weekly formula").Range(targetColumn & "93").Value = Worksheets("Cleaned").Range("b52").Value 
    Worksheets("Weekly formula").Range(targetColumn & "97").Value = Worksheets("Cleaned").Range("c52").Value 
    Worksheets("Weekly formula").Range(targetColumn & "95").Value = Worksheets("Cleaned").Range("d52").Value 
    Worksheets("Weekly formula").Range(targetColumn & "122").Value = Worksheets("Cleaned").Range("b53").Value 
    Worksheets("Weekly formula").Range(targetColumn & "126").Value = Worksheets("Cleaned").Range("c53").Value 
    Worksheets("Weekly formula").Range(targetColumn & "124").Value = Worksheets("Cleaned").Range("d53").Value 
    Worksheets("Weekly formula").Range(targetColumn & "151").Value = Worksheets("Cleaned").Range("b54").Value 
    Worksheets("Weekly formula").Range(targetColumn & "155").Value = Worksheets("Cleaned").Range("c54").Value 
    Worksheets("Weekly formula").Range(targetColumn & "153").Value = Worksheets("Cleaned").Range("d54").Value 
    Worksheets("Weekly formula").Range(targetColumn & "180").Value = Worksheets("Cleaned").Range("b56").Value 
    Worksheets("Weekly formula").Range(targetColumn & "184").Value = Worksheets("Cleaned").Range("c56").Value 
    Worksheets("Weekly formula").Range(targetColumn & "182").Value = Worksheets("Cleaned").Range("d56").Value 
    Worksheets("Weekly formula").Range(targetColumn & "209").Value = Worksheets("Cleaned").Range("b57").Value 
    Worksheets("Weekly formula").Range(targetColumn & "213").Value = Worksheets("Cleaned").Range("c57").Value 
    Worksheets("Weekly formula").Range(targetColumn & "211").Value = Worksheets("Cleaned").Range("d57").Value 
    
    Application.ScreenUpdating = True 
End Sub

Key Explanations: #

• Dim targetColumn As String: Declares a variable to hold the column letter (like "D", "AA", etc.) from Cleaned!H25.
• targetColumn = Worksheets("Cleaned").Range("H25").Value: Reads the value from H25 and stores it in our variable.
• targetColumn & "6": Combines the column letter with the row number to create a valid range reference (e.g., if H25 is "E", this becomes "E6").

Quick Notes: #

• Ensure Cleaned!H25 always contains a valid column letter (no blank cells or invalid text)—otherwise the macro will throw an error.
• I fixed a tiny typo in your original code: c211 was lowercase, now it's consistent with uppercase row numbers (VBA isn't case-sensitive, but consistency makes the code easier to read).

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