Looking for a VBA Code to copy/paste in a workbook onto a blank sheet

[Stevesmail]

Hello everyone

I have a workbook that has several sheets. Each sheet is named 123, & (A,-Z). See the screenshot I provided. This is a workbook that is being used in my place of employment to search Material Safety Data Sheets.

What I'm looking for is VBA to copy and paste column C in all worksheets in a workbook and paste onto a clean worksheet in the same workbook. I want the Material Names in Column C on each sheet to be be onto one page where it can be printed. I'm having trouble finding a macro that'll copy the pertinent values in column c on every sheet and bringing the info onto one page. I can easily put a macro to get this information from one sheet, but not several. The values I want copy and pasted all start on cell C7 on each sheet. The values I want copied can end on C20 or can end on C150. It all depends on what sheet is being pulled from. So the end cell isn't consistent from sheet to sheet, but is consistent at the starting cell which is C7. Any ideas I can work with? Thanks

 

[Anfinsen]

Hello everyone

I have a workbook that has several sheets. Each sheet is named 123, & (A,-Z). See the screenshot I provided. This is a workbook that is being used in my place of employment to search Material Safety Data Sheets.

What I'm looking for is VBA to copy and paste column C in all worksheets in a workbook and paste onto a clean worksheet in the same workbook. I want the Material Names in Column C on each sheet to be be onto one page where it can be printed. I'm having trouble finding a macro that'll copy the pertinent values in column c on every sheet and bringing the info onto one page. I can easily put a macro to get this information from one sheet, but not several. The values I want copy and pasted all start on cell C7 on each sheet. The values I want copied can end on C20 or can end on C150. It all depends on what sheet is being pulled from. So the end cell isn't consistent from sheet to sheet, but is consistent at the starting cell which is C7. Any ideas I can work with? Thanks

Hi Steve,
Assuming you add a new sheet at the beginning, and then run this macro, you would just need to loop through the sheets and perform the same step of the macro you described above.
Something like this;

Sub YourSub()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'insert your desired steps here, where you get the data and then paste to the new sheet
Next ws
End Sub