Lining up group of cell values in Excel 2007
Solved/Closed
PPattni
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014
-
Mar 13, 2014 at 03:10 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 31, 2014 at 11:23 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 31, 2014 at 11:23 AM
Related:
- Lining up group of cell values in Excel 2007
- Save as pdf office 2007 - Download - Other
- Mouse cursor not lining up - Guide
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Dd-mm-yyyy format in excel - Guide
7 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 17, 2014 at 12:57 PM
Mar 17, 2014 at 12:57 PM
Hi PPattni,
Do some test with the following code to see if it yields the desired result:
Best regards,
Trowa
Do some test with the following code to see if it yields the desired result:
Sub RunMe()
Dim Range1, Range2 As Range
Dim lRow As Integer
Set Range1 = Sheets("Sheet1").Range("D6:D8")
Set Range2 = Sheets("Sheet1").Range("G12:G15")
Sheets("Sheet2").Activate
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A3:A" & lRow)
If cell.Value = Sheets("Sheet1").Range("D6") Then
Range1.Copy
Range("A" & cell.Row).PasteSpecial Transpose:=True
Range2.Copy
Range("D" & cell.Row).PasteSpecial Transpose:=True
Range1.ClearContents
Range2.ClearContents
Exit Sub
End If
Next cell
Range1.Copy
Range("A" & lRow + 1).PasteSpecial Transpose:=True
Range2.Copy
Range("D" & lRow + 1).PasteSpecial Transpose:=True
Range1.ClearContents
Range2.ClearContents
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
PPattni
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014
Mar 25, 2014 at 07:07 AM
Mar 25, 2014 at 07:07 AM
Thanks for the reply Trowa
When I attached this code to the command button in sheet1
and run this code the I get a error message
Object doesnot support this Property or method.
If you can please check and let me know or if I am doing
any mistake.
Or if you can attach a file and send it to me.
Thanks
PPattni
When I attached this code to the command button in sheet1
and run this code the I get a error message
Object doesnot support this Property or method.
If you can please check and let me know or if I am doing
any mistake.
Or if you can attach a file and send it to me.
Thanks
PPattni
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 25, 2014 at 12:22 PM
Mar 25, 2014 at 12:22 PM
Hi PPattni,
Well, you didn't mention the use of a button. Which means we have to explicitly refer to a sheet other then the one the button is on.
But it didn't produce an error, so let me upload the file for you (with adjusted code).
Sheet2 has 3 rows of data.
Sheet1 has a button and data in the ranges D6:D8 and G12:G15.
After clicking the button, the data from sheet1 (a, bb, cc, dd, ee, ff, gg) should overwrite the second row of data on sheet2 (a, b, c, d, e, f, g), since column A matches the data from D6.
Here is the file:
http://speedy.sh/myv8N/PPattni-Lining-up-group-of-cell.xls
Do you get it to work now?
Best regards,
Trowa
Well, you didn't mention the use of a button. Which means we have to explicitly refer to a sheet other then the one the button is on.
But it didn't produce an error, so let me upload the file for you (with adjusted code).
Sheet2 has 3 rows of data.
Sheet1 has a button and data in the ranges D6:D8 and G12:G15.
After clicking the button, the data from sheet1 (a, bb, cc, dd, ee, ff, gg) should overwrite the second row of data on sheet2 (a, b, c, d, e, f, g), since column A matches the data from D6.
Here is the file:
http://speedy.sh/myv8N/PPattni-Lining-up-group-of-cell.xls
Do you get it to work now?
Best regards,
Trowa
PPattni
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014
Mar 26, 2014 at 01:42 AM
Mar 26, 2014 at 01:42 AM
I would say almost working Ok but need some changes.
1) Wanted all cells to be normal white and not in colours in
both the sheets.
2) The data in sheet1 should not get auto deleted. The values
in that cells has to be filled by me only when required else
the old or the last entered value remains as it is.
3) In sheet2 first two cells of every column I have reserved for
the column names. Here there is name entered from
D6 to G15 which I do not want.
4) The fourth row also has numbers 1 to 7 which needs to be deleted.
However the actual working of the code is perfect. It overwriting the s
similar value details and adding if it is new.
Thanks for helping
PPattni
1) Wanted all cells to be normal white and not in colours in
both the sheets.
2) The data in sheet1 should not get auto deleted. The values
in that cells has to be filled by me only when required else
the old or the last entered value remains as it is.
3) In sheet2 first two cells of every column I have reserved for
the column names. Here there is name entered from
D6 to G15 which I do not want.
4) The fourth row also has numbers 1 to 7 which needs to be deleted.
However the actual working of the code is perfect. It overwriting the s
similar value details and adding if it is new.
Thanks for helping
PPattni
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 27, 2014 at 12:10 PM
Mar 27, 2014 at 12:10 PM
Hi PPattni,
The file I sent you is the file I used to test the code. It is not how I foresee how your file looks like.
That being said, let's go over your points.
1) I colored and lined the two ranges so I know which data needs to be transferred (especially since I thought you wanted that data to be removed after transferring). The colors and lines are copied along with the data in the cells. So if your data doesn't have any colors or lines, then there won't be any on sheet2.
2) I interpreted transferring as cut/paste instead of copy/paste. There are 4 lines in the code with .ClearContents. Remove these lines to keep your data on sheet1. I will post the adjusted code at the end of this post.
3) I used those to know where the data was coming from. The code will look at the last row used and places the data 1 row below that. So if sheet2 is empty and the headers are placed in the first 2 rows then the first data being transferred will be placed at the 3rd row.
4) It's kind of a habit of me to test with both alphabetical and numerical values, since I don't know which values you will be using. So these numbers are just test values.
Let me know if something is still unclear.
Best regards,
Trowa
PS. here is the code from point 2:
The file I sent you is the file I used to test the code. It is not how I foresee how your file looks like.
That being said, let's go over your points.
1) I colored and lined the two ranges so I know which data needs to be transferred (especially since I thought you wanted that data to be removed after transferring). The colors and lines are copied along with the data in the cells. So if your data doesn't have any colors or lines, then there won't be any on sheet2.
2) I interpreted transferring as cut/paste instead of copy/paste. There are 4 lines in the code with .ClearContents. Remove these lines to keep your data on sheet1. I will post the adjusted code at the end of this post.
3) I used those to know where the data was coming from. The code will look at the last row used and places the data 1 row below that. So if sheet2 is empty and the headers are placed in the first 2 rows then the first data being transferred will be placed at the 3rd row.
4) It's kind of a habit of me to test with both alphabetical and numerical values, since I don't know which values you will be using. So these numbers are just test values.
Let me know if something is still unclear.
Best regards,
Trowa
PS. here is the code from point 2:
Private Sub cmdMoveData_Click()
Dim Range1, Range2 As Range
Dim lRow As Integer
Set Range1 = Sheets("Sheet1").Range("D6:D8")
Set Range2 = Sheets("Sheet1").Range("G12:G15")
Sheets("Sheet2").Activate
lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Sheet2").Range("A3:A" & lRow)
If cell.Value = Sheets("Sheet1").Range("D6") Then
Range1.Copy
Sheets("Sheet2").Range("A" & cell.Row).PasteSpecial Transpose:=True
Range2.Copy
Sheets("Sheet2").Range("D" & cell.Row).PasteSpecial Transpose:=True
Application.CutCopyMode = False
Exit Sub
End If
Next cell
Range1.Copy
Sheets("Sheet2").Range("A" & lRow + 1).PasteSpecial Transpose:=True
Range2.Copy
Sheets("Sheet2").Range("D" & lRow + 1).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub
PPattni
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014
Mar 31, 2014 at 06:36 AM
Mar 31, 2014 at 06:36 AM
Thanks a lot TrowaD you have solved my problem.
Do see you soon sometime in future if I get stuckup
with some other topic.
Thanks again
As a part of the procedure I have marked the topic as Solved.
Do see you soon sometime in future if I get stuckup
with some other topic.
Thanks again
As a part of the procedure I have marked the topic as Solved.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 31, 2014 at 11:23 AM
Mar 31, 2014 at 11:23 AM
You are very welcome PPattni!
Until we meet again.
Until we meet again.
