Overwriting cells when re-running macro

Hello,

I am using the following macro to split data from one worksheet into multiple worksheets within the same file. Basically, the macro takes the data that I have in column A and creates a separate tab for each different item found. It then copies all rows with that item name into the new tab. The first issue I have is that I want the data to copy to row 15 instead of row 2 in the new worksheets...in order to make room for header information. Secondly, I would like the previous data to be overwritten each time I re-run the macro instead of going down to the next available blank line. It is causing me to have duplicated data. Can someone help me add/change the code in this macro to do these things?

Thank you.



Sub Splitdatatosheets()

' Splitdatatosheets Macro

Dim rng As Range

Dim rng1 As Range

Dim vrb As Boolean

Dim sht As Worksheet

Set rng = Sheets("Query").Range("A2")

Set rng1 = Sheets("Query").Range("A2:I2")

vrb = False

Do While rng <> ""

For Each sht In Worksheets

If sht.Name = Left(rng.Value, 31) Then

sht.Select

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

ActiveCell.Offset(1, 0).Activate

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

vrb = True

End If

Next sht

If vrb = False Then

Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Left(rng.Value, 31)

Sheets("Query").Range("A2:I2").Copy ActiveSheet.Range("A2")

Range("A2").Select

Do While Selection <> ""

ActiveCell.Offset(1, 0).Activate

Loop

rng1.Copy ActiveCell

Set rng1 = rng1.Offset(1, 0)

Set rng = rng.Offset(1, 0)

End If

vrb = False

Loop

End Sub

You could try this code

Please Login or Register  to view this content.

Alf

Hi mumps1

Your code much better than mine!!

But time difference is a pain ... I checked before I posted and then there was no answer to this thread but when I now check I see that you posted 24 minutes earlier than me??? Strange

Took me a while before I found out there was a hidden sheet named "Misc." that the OP never mentioned. Tsk, tsk

Alf

Worked great! Thanks guys.

Hello. It's me again. The macro works great! However, I was wondering if someone could help me add one more thing. I need to add another worksheet called "Recon" that will add certain cells of the new worksheets together. So could I have the macro add the new worksheets between two pre-existing worksheets called "Beg" and "End"?

Thanks

Here is my updated file...

Thank you

Let me try that again

Perfect! Thank you!