Copy a cell value and paste it down a column on the same sheet and all sheets in the workbook

[Svandra]

Hi,

so, I'm a total Newbie to VBA and been working with it for 4 days now. So far, I could solve most of my question thanks to Google and trial and error but I'm stuck on the following:
I have a workbook full of sheets (20+). All workbooks look the same. In order to get the data Python readable I need to adjust columns. So, I have one cell in column C that holds a value. I need this value to be "transferred" to another column B and have it for all cells from B2:B. The code I wrote does nothing:

Sub copy_paste_to_all_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("C1").Copy
ws.Range("D:D").PasteSpecial
Next ws
End Sub

What Am I doing wrong? Any help is appreciated!

 

[Svandra]

Are you sure the code is in the workbook you want to change, that looks as though it's in your personal.xlsb workbook.

The code is supposed to work on all active workbooks, therefore I saved it as a personal.xlsb.

 

[Fluff]

Ok, how about

Sub copy_paste_to_all_sheets()
   Dim Ws As Worksheet
   
   For Each Ws In ActiveWorkbook.Worksheets
      Ws.Range("B2:B" & Ws.Range("I" & Rows.count).End(xlUp).Row).Value = Ws.Range("C1").Value
   Next Ws
End Sub

 

[Svandra]

Try this

Sub copy_paste_to_all_sheets()
Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
        ws.Range("B:B").Value = ws.Range("C1").Value
        Next ws
End Sub

I didn't see your reply right away, apologies! This doesn't do anything - not even a error message

 

[Fluff]

That's because it's working on your personal.xlb workbook.

 

[Svandra]

Ok, how about

Sub copy_paste_to_all_sheets()
   Dim Ws As Worksheet
  
   For Each Ws In ActiveWorkbook.Worksheets
      Ws.Range("B2:B" & Ws.Range("I" & Rows.count).End(xlUp).Row).Value = Ws.Range("C1").Value
   Next Ws
End Sub

This works like a charm. Thank you so much. I'm still trying to understand what is going on in this line "
Ws.Range("B2:B" & Ws.Range("I" & Rows.count).End(xlUp).Row).Value = Ws.Range("C1").Value" Can I get back to you with questions if needed?

 

[Fluff]

That line is put the value of C1 into col B starting from row 2 to the last row where col I has a value.