Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-27-2011, 02:31 PM
Novice Novice is offline Fill column with other columns info Windows 7 64bit Fill column with other columns info Office 2010 32bit
Novice
Fill column with other columns info
 
Join Date: Jul 2011
Posts: 6
Novice is on a distinguished road
Default 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
Attached Files
File Type: xlsx example.xlsx (10.3 KB, 14 views)
Reply With Quote
  #2  
Old 07-28-2011, 01:33 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

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?
Reply With Quote
  #3  
Old 07-28-2011, 06:05 AM
Novice Novice is offline Fill column with other columns info Windows 7 64bit Fill column with other columns info Office 2010 32bit
Novice
Fill column with other columns info
 
Join Date: Jul 2011
Posts: 6
Novice is on a distinguished road
Default

Quote:
Originally Posted by Catalin.B View Post
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.
Reply With Quote
  #4  
Old 07-28-2011, 06:49 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm example.xlsm (18.6 KB, 9 views)
Reply With Quote
  #5  
Old 07-28-2011, 07:06 AM
Novice Novice is offline Fill column with other columns info Windows 7 64bit Fill column with other columns info Office 2010 32bit
Novice
Fill column with other columns info
 
Join Date: Jul 2011
Posts: 6
Novice is on a distinguished road
Default

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
File Type: xlsx example2.xlsx (10.0 KB, 7 views)
Reply With Quote
  #6  
Old 07-28-2011, 08:09 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm example.xlsm (20.2 KB, 9 views)
Reply With Quote
  #7  
Old 07-28-2011, 08:38 AM
Novice Novice is offline Fill column with other columns info Windows 7 64bit Fill column with other columns info Office 2010 32bit
Novice
Fill column with other columns info
 
Join Date: Jul 2011
Posts: 6
Novice is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 07-28-2011, 09:25 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm example.xlsm (20.7 KB, 10 views)
Reply With Quote
  #9  
Old 07-28-2011, 09:36 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

How is the weather in Spain? Does it rain? Comes mainly in the plain?
( This is a joke )
Reply With Quote
  #10  
Old 07-28-2011, 09:39 AM
Novice Novice is offline Fill column with other columns info Windows 7 64bit Fill column with other columns info Office 2010 32bit
Novice
Fill column with other columns info
 
Join Date: Jul 2011
Posts: 6
Novice is on a distinguished road
Default

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.
Reply With Quote
  #11  
Old 07-28-2011, 09:45 AM
Catalin.B Catalin.B is offline Fill column with other columns info Windows Vista Fill column with other columns info Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

Glad to help, Good Luck!
Reply With Quote
Reply

Thread Tools
Display Modes


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
Fill column with other columns info 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
Fill column with other columns info How to expand one column into two columns? Jamal NUMAN Excel 1 01-12-2011 07:59 PM
Fill column with other columns info How to compare 2 columns with other two columns in EXECL 2007? Learner7 Excel 5 06-12-2010 09:54 AM
Fill column with other columns info Graphing in Excel - one plot many columns/pick one column jerryjaysr Excel 4 06-29-2006 07:27 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft