Copy and Insert/Overwrite

Hi Experts,

Have the following piece of code that I need to adjust.

Basically, the code copy's the named range 'SiteFlows', finds a match to what is in cell A11, then inserts 'SiteFlows' below the match within the range of A61:A65536.

However, if I execute the code twice, it obviously copies the 'SiteFlows' range again below the match and below the already copied data. How can I adjust the code to be a little smarter so that if I have already copied and inserted before, it just copies and overwrites the existing data underneath the match.

I have attached a example for assistance and hoped I haven't confused you too much. :-)
http://s6.quicksharing.com/v/1102894/.html 


Public Sub CopyAndInsert_SiteFlows()

   Dim Row As Long
   
   With ActiveSheet
      Row = Application.Match(.[A11], .[A61:A65536], 0)
      If Row > 0 Then
         .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Insert
         [SiteFlows].EntireRow.Copy .Rows(Row + 61).Resize([SiteFlows].Rows.Count)
        .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value = .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value
      End If
   End With

End Sub

Regards

LK
lkirkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
The problem with your request is there is no way to determine if the key found was previously copied. Unless there is something you are not telling us?

There is no file to download at the URL you provided.

Kevin
lkirkeAuthor Commented:
Hi Kevin,

Just wait 20secs for the download to kick in then click on Download File.

Regards

LK
zorvek (Kevin Jones)ConsultantCommented:
I did...it says there is nothing there.

Kevin
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

zorvek (Kevin Jones)ConsultantCommented:
Files can be posted any of a number of ways.

The prefered method at this time is to use the Experts-Exchange file sharing site. It is available to all registered Experts-Exhange members and it is free. First zip the file or files to be posted. Then navigate to http://www.ee-stuff.com, click Login in the upper right corner, enter your Experts-Exhange user name and password, click the Login command button, navigate to Expert Area, and click "Upload a new file". Enter the complete URL of the question, or the Question ID into the text box labeled "Question", and then click "Browse..." to select the desired file to upload. The question ID is the eight-digit number after the "Q_" in the question URL. Type a comment describing the file. Click "Upload" to upload the file. A page will then be presented with a URL that can be posted in the Experts-Exchange question so that others can download the file.

There are other free file sharing services available:

Geocities: http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html
AngelFire: http://www.angelfire.lycos.com/doc/subscriptions/index.html
Google: http://base.google.com/base/default 
RapidUpload: http://www.rapidupload.com/

If you do not want to use any of these services you can send your file to me using the email address in my profile (http://www.experts-exchange.com/M_1677072.html) and I will post it on my own site.

Kevin
lkirkeAuthor Commented:
Thanks for that.

Try this link.

http://www25.rapidupload.com/d.php?file=dl&filepath=10961 

Regards

LK
lkirkeAuthor Commented:
Kevin,

As you might see from the file, there is a 2 line gap between each SITE. Could we basically say that if the gap is greater than 2 lines, delete, copy then insert ?

Regards

LK
zorvek (Kevin Jones)ConsultantCommented:
Try this version:

Public Sub CopyAndInsert_SiteFlows()

   Dim Row As Long
   
   With ActiveSheet
      Row = Application.Match(.[A11], .[A61:A65536], 0)
      If Row > 0 Then
         If Len(.Cells(Row + 61)) > 0 Then
            .Cells(Row + 61).Resize([SiteFlows].Rows.Count).EntireRow.Delete
         End If
         .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Insert
         [SiteFlows].EntireRow.Copy .Rows(Row + 61).Resize([SiteFlows].Rows.Count)
        .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value = .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value
      End If
   End With

End Sub

Kevin
lkirkeAuthor Commented:
Still having the same issue Kevin.
zorvek (Kevin Jones)ConsultantCommented:
Yup...I tested this version :-)

Public Sub CopyAndInsert_SiteFlows()

   Dim Row As Long
   
   With ActiveSheet
      Row = Application.Match(.[A11], .[A61:A65536], 0)
      If Row > 0 Then
         If Len(.Cells(Row + 61, 1)) > 0 Then
            .Cells(Row + 61, 1).Resize([SiteFlows].Rows.Count).EntireRow.Delete
         End If
         .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Insert
         [SiteFlows].EntireRow.Copy .Rows(Row + 61).Resize([SiteFlows].Rows.Count)
        .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value = .Rows(Row + 61).Resize([SiteFlows].Rows.Count).Value
      End If
   End With

End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lkirkeAuthor Commented:
You are a god. :-)

Thankyou Kevin.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.