Excel Macro Help.

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!

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

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!

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

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 :)