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!

 

[shinigamilight]

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

 

[Fluff]

Do you really want to copy the value of C1 into all 1,048,576 cells of column B, or only some of them?

 

[Svandra]

Do you really want to copy the value of C1 into all 1,048,576 cells of column B, or only some of them?

Took me a second to understand what you're talking about You're right, I just want the data corresponding to all the entries of column I (I put the wrong column in the code - unfortunately, it's still not doing anything).

 

[Fluff]

Is the code located in the same workbook as the one where you want to copy the values?

 

[Svandra]

Is the code located in the same workbook as the one where you want to copy the values?

yes, same workbook, same sheet.

 

[Fluff]

In that case your code should work, albeit with the wrong column. In what way isn't it working?

 

[Svandra]

In that case your code should work, albeit with the wrong column. In what way isn't it working?

it's doing nothing. just nothing

 

[Svandra]

it's doing nothing. just nothing

now it's giving me this error message:

 

[Fluff]

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