Excel Macro Help.

Solved/Closed
Felicity86 Posts 4 Registration date Wednesday May 18, 2011 Status Member Last seen May 27, 2011 - May 18, 2011 at 10:05 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - May 27, 2011 at 09:24 AM
Hi, I was wondering if someone could help me develop a macro that would reference a row in another worksheet.

So for example if Column H = "x" in Sheet 1 then the preceding data in that row are referenced in Sheet 2.

I know there are quite a few threads on copying, but I was hoping that if I update any of the details in Sheet 1 that they will then be updated in sheet 2. I thought referencing might be the best way to do this? Any help would be much appreciated! Let me know if you need more detail!

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
May 19, 2011 at 10:15 AM
Hi Felicity,

The following code will look at column H for the value "x". When found it will copy the entire row to sheet 2 and then look for the next "x".
To prevent duplicates in sheet 2, the entire sheet is cleared before pasting.
The code will only tricker when a change is made to column H of sheet 1.

Try it out, here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [H:H]) Is Nothing Then Exit Sub
Set MR = [H:H]
Sheets("Sheet2").Cells.ClearContents
For Each cell In MR
If cell.Value = "x" Then
cell.EntireRow.Copy
lRow = Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Sheet2").Range("A" & lRow).PasteSpecial
    End If
        Next
Application.CutCopyMode = False
End Sub

To implement it, right-click on the tab of sheet 1 and select view code. Now copy paste the code.

Now test it by changing a value in column H of sheet 1.

Best regards,
Trowa
1
Felicity86 Posts 4 Registration date Wednesday May 18, 2011 Status Member Last seen May 27, 2011
May 19, 2011 at 05:49 PM
Thats fantastic thankyou!

Just a few quick things :)

Is there a way that it only copies columns a-g' instead of the entire row?

There is a significant amount of data in this spreadsheet, is there a way that i can create a button that will run the macro rather than it running every time i enter a piece of data?

Finally, do i just repeat the Macro if i want sayd the next column to assign data to the next sheet? so for example column H has an x copy this to sheet 2, column I has an x copy to sheet 3 and so on?

I'm being really picky now so many apologies but i just wanted to know if this was possible? :)

Thanks!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
May 20, 2011 at 10:39 AM
Hi Felicity,

Don't hold back on asking questions to make Excel do what you want!

The answer to your questions is a triple yes.
I adjusted the code for you:

Sub MoveData()
Set MR = Sheets("Sheet1").[H:H]
Set MR2 = Sheets("Sheet1").[I:I]

Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet3").Cells.ClearContents

For Each cell In MR
If cell.Value = "x" Then
Sheets("Sheet1").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    End If
        Next
        
For Each cell In MR2
If cell.Value = "x" Then
Sheets("Sheet1").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    End If
        Next
        
Application.CutCopyMode = False
End Sub

Question 1:
You will see that the line "cell.EntireRow.Copy" has been replaced by "Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy"

Question 3:
I am sure there are better ways of handling this, but this is how I did it.
Set another MR (My Range). "Set MR2 = Sheets("Sheet1").[I:I]"
Clear the contents of the next sheet. "Sheets("Sheet3").Cells.ClearContents"
Add another For Each block. Change MR to MR2 and ("Sheets2") to ("Sheets3").
"
For Each cell In MR2
If cell.Value = "x" Then
Sheets("Sheet1").Select
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Copy
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
"
Repeat these steps as many times as necessary.

Question 2:
I changed the line "Set MR = [H:H]" to "Set MR = Sheets("Sheet1").[H:H]" and added the line "Sheets("Sheet1").Select" below the line "If cell.Value = "x" Then" so the code can now be run from every sheet.
The code needs to be pasted in another area.
Open VB by hitting ALT+F11.
Goto top menu > Insert > Module.
Paste the code in the big white field.
You can now choose to run the code in three ways:
1. ALT+F8 will display the available macro's. Choose the right one and hit execute.
2. Applying a shortcut key. Hit ALT+F8 and choose Options. Here you can choose a shortcut key.
3. Create a button. In Excel right-click below the top menu's. This will list the available ribbons. (I'm using a dutch version of excel and don't know the exact translation.) Choose the one below Web and above WordArt, it's the 4th option from the bottom. Something with Toolset and elements. Anyway on of the options of this ribbon is the Command button. Click on it and draw your button.
Right-click on the button and select view code. Back in VB two code lines are already given to you. You can now choose to call the code by typing "Call MoveData" between the code lines or paste the entire code between the two code lines.

Hopefully my explanation is clear.

Feel free to ask away. :)

Best regards,
Trowa
0
Felicity86 Posts 4 Registration date Wednesday May 18, 2011 Status Member Last seen May 27, 2011
May 25, 2011 at 08:48 PM
Sorry about the delay in response - very busy week at work! :S and I don't want you to think I'm unappreciative!

I just have one last thing and my spreadsheet will be perfect.

Is there a way where the sheet where the cells are copied to I can keep the row header, rather then clear that when I run the Macro? and possibly keep the information in rows H? So say when sheet 2 updates columns A-G update but everything else in the spreadsheet is kept.

I'm sure it has something to do with this part of the code:

Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet3").Cells.ClearContents

Thanks again - Legend!
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
May 26, 2011 at 10:21 AM
Thanks for your reply, it's nice to be appreciated once in a while. :)

You are right about the part of the code. Change "Sheets("Sheet2").Cells.ClearContents"
into:
lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row 
Sheets("Sheet2").Range("A2:G" & lRow).ClearContents 

lRow determines the last row being used. As you can see I used the letter A. This means that column A needs to be filled with data in order to clear the sheet. Change this letter if column A remains empty.

Repeat for sheet3 and any other sheets you might add.

Kind regards,
Trowa
0
Felicity86 Posts 4 Registration date Wednesday May 18, 2011 Status Member Last seen May 27, 2011
May 27, 2011 at 01:10 AM
Works a treat. Thanks again :)

and unfortunately I have one last question (you can't get rid of me!) Is there a way to make this worksheet so that multiple people can be working on it at once.

I didn't think there would be but thought it was worth asking :)
0