PDA

View Full Version : Solved: Import column c !



Mergh06
10-17-2011, 07:10 AM
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
10-17-2011, 08:43 AM
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

Mergh06
10-17-2011, 09:08 AM
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
10-17-2011, 09:30 AM
When you test things like this, be sure to work on a backup copy.


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

To each column C:
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

Mergh06
10-17-2011, 10:01 AM
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
10-17-2011, 10:12 AM
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.

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

Mergh06
10-17-2011, 10:16 AM
omg it works:) thanks so much...
you are right about its the half the solution, sorry about that...

Aussiebear
10-18-2011, 02:46 AM
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.