Solved: Import column c !

[Mergh06]

Hello guys,
Hope you can help with this one:
I need to import column C's from all sheets in a rawdata-workbook to a main workbook (they are in the same directory)


In short i need:
-Import all data from C2 and and up from all sheets
-sheet name has to be C1

There is alot of worksheets!

Hope you can help,
Thank you.
Mergh

[Kenneth Hobs]

[VBA]Sub ImportAllColCs()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open(ThisWorkbook.Path & "\text.xlsm")
For Each ws In wb.Worksheets
ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Copy _
ThisWorkbook.ActiveSheet.Range("C" & Rows.Count).End(xlUp).Offset(1)
Next ws
wb.Close False
End Sub[/VBA]

[Mergh06]

Thank you for taking your time,
But each column C's have to be puttet in the main workbook in each columns..

For example
sheet 1 column C in main workbook column A
sheet 2 column C in main workbook column B
sheet 3 column C in main workbook column C
sheet 4 column C in main workbook column D
and so on..

And the Sheet names have to be puttet in the A1, B1, C1, D1 and so on

[Kenneth Hobs]

When you test things like this, be sure to work on a backup copy.


[VBA]Sub ImportAllColCsToSheetIndexes2()
Dim wb As Workbook, ws As Worksheet, col As Integer
Set wb = Workbooks.Open(ThisWorkbook.Path & "\text.xlsm")
For Each ws In wb.Worksheets
col = ws.Index
ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Copy _
ThisWorkbook.Worksheets(col).Cells(Rows.Count, col).End(xlUp).Offset(1)
ThisWorkbook.Worksheets(col).Cells(1, col).Value2 = ws.Name
Next ws
wb.Close False
End Sub[/VBA]

To each column C:
[vba]Sub ImportAllColCsToSheetIndexes()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open(ThisWorkbook.Path & "\text.xlsm")
For Each ws In wb.Worksheets
ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Copy _
ThisWorkbook.Worksheets(ws.Index).Range("C" & Rows.Count).End(xlUp).Offset(1)
ThisWorkbook.Worksheets(ws.Index).Range("C1").Value2 = ws.Name
Next ws
wb.Close False
End Sub[/vba]

[Mergh06]

hi, hope not to be too annoying..

but it do not work... it only copy the first sheet to column C in the main sheet... (but the label work in C1)....

But i need all the other sheets C-columns copied to column A,B,C,D,E,F,G... columns in the main workbook

[Kenneth Hobs]

As you can see, defining your problem is half the solution. Had you said:

For example
sheet 1 column C in slave workbook to master workbook sheet 1 column A
sheet 2 column C in slave workbook to master workbook sheet 1 column B
sheet 3 column C in slave workbook to master workbook sheet 1 column C
sheet 4 column C in slave workbook to master workbook sheet 1 column D

it would have been more clear.

[vba]Sub ImportAllColCsToSheetIndexes3()
Dim wb As Workbook, ws As Worksheet, col As Integer
Set wb = Workbooks.Open(ThisWorkbook.Path & "\text.xlsm")
For Each ws In wb.Worksheets
col = ws.Index
ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Copy _
ThisWorkbook.Worksheets(1).Cells(Rows.Count, col).End(xlUp).Offset(1)
ThisWorkbook.Worksheets(1).Cells(1, col).Value2 = ws.Name
Next ws
wb.Close False
End Sub
[/vba]

[Mergh06]

omg it works thanks so much...
you are right about its the half the solution, sorry about that...

[Aussiebear]

omg it works thanks so much...
you are right about its the half the solution, sorry about that...

Its not at all about ".... half the solution''' at all, its very clearly about the description of the issue.