Lining up group of cell values in Excel 2007

[Solved/Closed]
Report
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
Hi
I have two group of cells D6 to D8 and other G12 to G15
Need a code with two conditions that can line up the details of
the mentioned cells in sequence in the second sheet from
column A3 onwards row wise.

First condition
When the code is run the data transferrs to second sheet.
A3 will have D6 details
B3 will have D7 details
C3 will have D8 details
D3 will have G12 details
E3 will have G13 details
F3 will have G14 details
G3 will have G15 details
When the code is run again the second details will be from
A4 to G4 and so on and it adds one below the other.

Second Condition
When the data is transferred to the second sheet and if only in
column A already a same value is there then it
overwrites along the entire row details.

Thanks
PPattni

7 replies

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi PPattni,

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
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014

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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014

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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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:
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
Posts
4
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 31, 2014

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.
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
You are very welcome PPattni!

Until we meet again.