Hi


i came across this macro, since its similar to what i want

Sub Encolumnar()
Dim C As Range
[p]. Delete
For Each C In Selection
If IsEmpty(C. Offset(1)) Then
C. Copy Range("p" & Cells.Rows.Count). End(xlUp). Offset(1)
Else
Range(C, C. End(xlDown)). Copy Range("p" & Cells.Rows.Count). End(xlUp). Offset(1)
End If
Next C
End Sub

is there a way to change this so that the selection is made in a column instead of a row, plus other columns on others sheets? :S

The main objective was to do something like this without macros, but if it cant then i'll use this macro.

The main idea here is in the excel im adding

Attached Files

example.xlsx (10.3 KB, 16 views)

what do you mean with "data is variable": the format, or text data, numeric data..
Give us some details please. DAta are in other sheets in the same workbook? or in other workbooks?

1. The info in the PERSONEL's columns, those can change. Sometimes they will have more or less personel in the company.
2.And they are in the same workbook.

Take a look at the example worksheet, see how it works.
The macro depends on Cells B5 si B6 marked in yellow for column number and worksheet to copy from, you can change them as you need. The macro looks like this:

Sub SchimbaCap2()
Dim col As Range, row As Range, Myrange As Range
Set col = Worksheets("Hoja1").Range("B1")

If Worksheets("Hoja1").Range("B2") = 0 Then
MsgBox " Type in cell B6 the name of the source worksheet!"
Exit Sub
End If

Set Myrange = Range(Worksheets(Worksheets("Hoja1").Range("B2").V alue).Cells(5, col), Worksheets(Worksheets("Hoja1").Range("B2").Value). Cells(15, col))
Myrange.Copy
Worksheets("Hoja3").Range("A5").PasteSpecial (xlPasteValues)

Application.CutCopyMode = False
Worksheets("Hoja3").Activate
On Error GoTo 0

End Sub

Attached Files

example.xlsm (18.6 KB, 11 views)

Ohh, this seems to be the way to follow because it grabs the info from i sheet and takes it to another (Y).

"But" the thing is that i need to take from the 3 personel columns in different sheets and join them on the data column.

Check the example2

Thnx in advance
MS

Attached Files

example2.xlsx (10.0 KB, 9 views)

Try this (same macro in sample attached):
Sub SchimbaCap2()
Dim Myrange As Range
Dim nextRow As Long

Dim r1, r2, r3 As Range
Set r1 = Sheets("Hoja2").Range("A5:A15")
Set r2 = Sheets("Hoja3").Range("A5:A15")
Set r3 = Sheets("Hoja4").Range("A5:A15")
Application.ScreenUpdating = False
Set Myrange = r1
nextRow = Sheets("Hoja5").Range("A" & Rows.Count).End(xlUp).row + 1
Myrange.Copy
Sheets("Hoja5").Range("A" & nextRow).PasteSpecial (xlPasteValues)

Set Myrange = r2
nextRow = Sheets("Hoja5").Range("A" & Rows.Count).End(xlUp).row + 1
Myrange.Copy
Sheets("Hoja5").Range("A" & nextRow).PasteSpecial (xlPasteValues)

Set Myrange = r3
nextRow = Sheets("Hoja5").Range("A" & Rows.Count).End(xlUp).row + 1
Myrange.Copy
Sheets("Hoja5").Range("A" & nextRow).PasteSpecial (xlPasteValues)

On Error GoTo 0
Application.CutCopyMode = False
Worksheets("Hoja5").Activate
Application.ScreenUpdating = True
End Sub

Attached Files

example.xlsm (20.2 KB, 11 views)

omg you are the best (Y), somehow now im being asked for more <.< i'll try to comprehend what u did to adapt it to what i need now.

The problem now is that theres a blank space between the names in the columns. i used ur macro but it grabs also the blanks spaces.

After the line : Worksheets("Hoja5").Activate


insert this:


Dim i As Integer
For i = 2 To 100
If Sheets("Hoja5").Range("A" & i).Value = 0 Then
'delete empty rows
Rows(i & ":" & i).EntireRow.Delete

End If
Next i


If you replace "Delete" from above to "Hidden = True", it will just hide empty rows, instead of deleting them

Attached Files

example.xlsm (20.7 KB, 12 views)

How is the weather in Spain? Does it rain? Comes mainly in the plain?
( This is a joke )

Really really thank you, you have helped me a lot! You dont know how much (Y)!
I'll try to learn from this

P.D.: btw im not from spain xD! im from Panama, yes the one that connects south america with north america. lol.

Glad to help, Good Luck!