Copy cell from on sheet to another If the cells starts with **

[John Swift]

Hello and thank you for your help in advance any input is greatly appreciated.

Basically (or not so in my case) I was wondering if it possible to create a Macro that would Copy and Paste Data from one sheet to another in the same Workbook if the the cell starts with AA,BB,CC or DD and Put that "Data" in its already named sheet. The beginning of each cell can change so some times the data my be AB or CE or DE depending on the situation but the data will only be in four different iterations i.e. CD EG GH HH and so on. (Ican chge this in the macro)

Example of the data I have

From a sheet named "Data" (All Data is in Column A and starts from Row 1 and can be unto 10,000 Rows in length)

BB01465178
AA11111547
AA13579456
BB45765135
CC45612347
DD46785947
DD13546878
CC17846597

Result

Sheet "Data AA"

AA11111547
AA13579456

Sheet "Data BB"

BB01465178
BB45765135

Sheet "Data CC"

CC17846597
CC45612347

Sheet "Data DD"

DD13546878
DD46785947

This Macro would ideally search though all of Columns A and find all the Cells starting with AA from sheet "Data" and copy them into a sheet named "AA Data" into Column A then copy all the Cells that start with BB from sheet "Data" and copy them into the sheet "BB Data" into Column A and so on till all four sheets have been done.

In regards to how the macro does this it really does not matter i.e. if it deletes the data or leaves it or even if the macro only works with one set of data and I have to run the macro 4 separate times it does not matter all long as the outcome is the same.

Again I really appreciate any input into this matter and thank you for your help.

[Trebor76]

Hi John,

You may have to tweak my following code as at one point you say to distribute the data to sheet "Data AA" and then you say to send it to sheet "AA Data" - my code is based on the former:

Please Login or Register  to view this content.

Regards,

Robert

[John Swift]

Thank you for your help

I should add that I cant code in VBA well and your macro I have no idea what to do with it.

But I still do appreciate the help.

Thank you.

[Crooza]

Try this macro

Please Login or Register  to view this content.

[Trebor76]

To insert the macro into the relevant workbook follow these four steps:

1. Copy my macro to the clipboard (Ctrl + C [note this notation means to press and hold the first key followed by the second key])
2. In the workbook where you want the macro press Alt + F11
3. Paste (Ctrl + V) in my macro from the clipboard (step 1 above) into the module
4. From the File menu select Close and Return to Microsoft Excel

To use the macro just follow this step:

Press Alt + F8 and from the Macro dialog click on MoveData and then click on the Run button

HTH

Robert

[John Swift]

Thank you for you help guys it works perfectally

[Trebor76]

You're welcome and thanks for marking the thread as solved (and the rep)

[Crooza]

No problem