#1
|
|||
|
|||
Fill column with other columns info
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 |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
Quote:
2.And they are in the same workbook. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
How is the weather in Spain? Does it rain? Comes mainly in the plain?
( This is a joke ) |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
Glad to help, Good Luck!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to create two columns based on a certain criterior from the original two column? | where_pc | Excel | 3 | 06-13-2011 10:39 AM |
Need to search a column for a macth and return a result from a third column | pdfaust | Excel | 2 | 02-03-2011 03:02 PM |
How to expand one column into two columns? | Jamal NUMAN | Excel | 1 | 01-12-2011 07:59 PM |
How to compare 2 columns with other two columns in EXECL 2007? | Learner7 | Excel | 5 | 06-12-2010 09:54 AM |
Graphing in Excel - one plot many columns/pick one column | jerryjaysr | Excel | 4 | 06-29-2006 07:27 AM |