• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with VBA copying and inserting a header!

T

Teatimedgg

New Member
Hey Everyone,

Newby here! I have a multiple columns of data but i want to insert a header line (1st line -range A2.H2) when the word Dog-A changes in Column C i want this to continue until the end of data in column c

Example:
HABC companyHeader1234512312345012
LABC companyDog***xUnit1
LABC companyDog***xUnit119.03
LABC companyDog-A***xUnit1200
LABC companyCat***xUnit170
LABC companyCat***xUnit115000

Any help would be appreciated!!! Thank you!
 

Attachments

Hi ,

Can you upload a workbook which shows your data as it is in one tab , and as it should be after the macro runs , on another tab ?

Narayan
 
Hi:

Something like this?
Code: 
Sub test()
Application.ScreenUpdating = False

Dim Rng As Range

On Error Resume Next
i& = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Sheet3.Range("A2:H" & i)

For j& = Rng.Rows.Count - 1 To 2 Step -1
    If Rng.Cells(j, 3).Value = "Dog-A" Then
        Rng.Cells(j + 1, 1).EntireRow.Insert
        Range("A2:H2").Copy
        Range("A" & k & ":H" & k).PasteSpecial
        Application.CutCopyMode = False
    End If
    k = j + 1
Next
Application.ScreenUpdating = True
End Sub

Thanks
 

Attachments

Hi ,

Another option :
Code: 
Public Sub InsertHeader()
          Dim Headerrow As Range, rw As Range
          Dim lastrow As Long
         
          Application.ScreenUpdating = False
         
          With Me
                If .AutoFilterMode Then .Cells.AutoFilter
               
                Set Headerrow = .Range("A2:H2")
                Headerrow.Font.Bold = True
                lastrow = .Range("A" & Rows.Count).End(xlUp).Row
                With .Range("A2:H" & lastrow)
                    .AutoFilter Field:=Application.Match("Header", Headerrow, 0), Criteria1:="Dog-A"
                    Set filtereddatarange = .SpecialCells(xlCellTypeVisible)
                End With
               
                If filtereddatarange Is Nothing Then Exit Sub
               
                With filtereddatarange
                    For Each rw In .Rows
                        With rw
                              If .Address <> Headerrow.Address Then
                                .Offset(1).EntireRow.Insert
                                .Offset(1).Value = Headerrow.Value
                                .Offset(1).Font.Bold = True
                              End If
                        End With
                    Next
                End With
               
                .Cells.AutoFilter
                .Columns.AutoFit
          End With
         
          Application.ScreenUpdating = True
End Sub
Narayan
 

Attachments

Hi ,

That can only happen if you have copied the code and pasted it into a code module , instead of a Sheet section.

Narayan
 
Hi ,

Paste the code in a Sheet section , the worksheet where your data is.

If your data is in a sheet named Sheet5 , paste the code into the Sheet5 section in your VBE.

You can always retain the code in a code module , by replacing all references to the Me keyword with the ActiveSheet keyword. You will have to ensure that you make the sheet which has your data the active sheet before you run the macro.

Narayan
 
Ok updated With Me to With ActiveWorkbook.Worksheets("GP UPLOAD"). I changed the Dog-A name to the correct name and I get error ; autofilter method of range class failed - UGH I appreciate your help
 
Hi ,

I am sorry , but unless you can upload your actual workbook , I cannot help.

Is the column header label Header ? If not , you need to change the highlighted words in the following line of code :

.AutoFilter Field:=Application.Match("Header", Headerrow, 0), Criteria1:="Dog-A"

If after this change , it still does not work , please upload your working file.

Narayan
 
Hi ,

Can you clarify at what point the header values should be inserted ?

Is it after the lines which have MEDIA in the Header column , or is it after the lines which have CORP-MMF in the Header column ?

Narayan
 
It is after CORP-MMF. I ran the other code, and if I only have 15 lines it work. I the amount of rows will always be different. Can it keep going until the end of data?
 
You must log in or register to reply here.
Back
Top