VBA coding to allow for an insert of a row and to pull data from one sheet to specific columns in another

On the Title_Frame_Register sheet of the Excel File that was attached, whenever a new row is inserted, it is not being accounted for on the corresponding Revisions sheet in the Layout Tab.  

In other words, we are pulling the Layout Tab column from Title_Frame_Register to the Layout Tab column on the Revisions sheet.  But if a user inserts a new row on Title_Frame_Register, we don't know how to account for that on the Revisions sheet in the Layout Tab.  

It is not adding the new row or the data.

Also, when we are adding information on the Revisions sheet in the Revision Date, Revision Description and Checked By columns and are all done, we would like the information clear and then to update the Title_Frame_Register sheet in the corresponding columns.  So if there is information in those columns on the Title_Frame_Register page (AV) for example, it would know there is data in those columns and it would know to go to (AZ) and so on.  

I have not done VBA much  in years and was really good at so any assistance offered would be greatly appreciated and anything I can provide for clarity I will certainly try.  

LayoutTab.jpg
Revision.jpg
regsampAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Saurabh Singh TeotiaCommented:
Hey Reg, the file is missing..Can you upload your file..also i will be traveling in the evening so won't get chance to look at it for couple of hours...

Saurabh...
regsampAuthor Commented:
Yes, I am sorry about that. I don't know why it did not attach before. Thank you.
Title_Frame_Register-Master--3-.xls
Saurabh Singh TeotiaCommented:
Looking at your file..i need more context about what you are trying to..i'm not clear about what you are trying to achieve here..Can you show me in the above file by the process flow what you want to do and i can write a  macro for you to do the same...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

regsampAuthor Commented:
Step 1/Request1: How do I replicate (copy a column contents of Values to another sheet column) of a whole column (with data content) from a primary sheet to an secondary sheet in another column?

When primary sheet is updated (populated with data) the values of replicated column are automatically updated in the column of the other sheet? This would include if a new row was inserted on the primary sheet.  (So the column is B on the sheet of "Title_Frame_Register" to column A of the "Revisons" sheet)  

I tried a Function but it is not accounting for an insert of a new row on the primary sheet ("Title_Frame_Register")

Step 2/Request2: I am not even sure if this can be done but on the "Revisions" sheet, when data is entered into the columns of B, C and D, a button can then be pressed that clears all the data in these columns and puts it into the "empty" columns only in "Title_Frame_Register" of the columns that have "Revision Date", "Revision Description" and "Checked By" in the next empty columns with those words in the cells. So it would be AU, AV and AX on "Title_Frame_Register" sheet.
Revision.jpg
If there had been data in the above columns,  the macro would put the data in AZ, BA and BB of the "Title_Frame_Register" sheet.
It is confusing as I had to have the users say it a few times. I will try and clear up anything you need. Thank you for the help.
Revision2.jpg
regsampAuthor Commented:
I have tried below but I am getting an expected end of statement error on the Dim part...

 Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 2) Then
        Dim row as Integer = Target.Row
        Revisions.Cells(row, "B") = Target.Value
        End If
End Sub
regsampAuthor Commented:
It looks like this is as close to copying a column from one sheet to another. It does close to what I was looking for:

Sub CopyNoBanks()
Dim sh1 As Worksheet, s2 As Worksheet, i As Long
    Dim j As Long, N As Long, r1 As Range

    Set sh1 = Sheets("Title_Frame_Register")
    Set sh2 = Sheets("Revisions")
    N = sh1.Cells(Rows.Count, "B").End(xlUp).Row
    j = 1

    For i = 1 To N
        Set r1 = sh1.Cells(i, "B")
        If r1.Value <> "" Then
            r1.Copy sh2.Cells(j, "A")
            j = j + 1
        End If
    Next i
End Sub
regsampAuthor Commented:
The last part I just need help is adding code to the macro to copy from the "Revisions" sheet, the columns of B, C, and D into the Title_Frame_Register columns with the Headers, "Revision Date", "Revision Description", and "Checked By".  

If the columns have data in them, they would move to the next empty columns with those headers.  

I have something like this in mind but don't know enough to finish it.

Sheets("Sheet1").Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2)
aikimarkCommented:
Try this
Option Explicit


Sub Q_28727859()
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Dim rngSrc As Range
    Dim rngTgt As Range
    
    Set sh1 = Sheets("Title_Frame_Register")
    Set sh2 = Sheets("Revisions")
    Set rngTgt = sh2.Range("A4")
    Set rngSrc = sh1.Range("B5")
    Set rngSrc = sh1.Range(rngSrc, sh1.Cells(sh1.Rows.Count, 2).End(xlUp))
    rngSrc.Cells.SpecialCells(xlCellTypeConstants).Copy rngTgt
    
End Sub

Open in new window

Please note that none of your modules requires explicit variable declaration.  You should add an Option Explicit statement to all of your modules and compile your VBProject.
regsampAuthor Commented:
I am sorry for the confusion. I got the column over from the first Sheet.

 I am trying to get a button that copies from the "Revisions" sheet, the columns of B, C, and D into the Title_Frame_Register sheet columns with the Headers, "Revision Date", "Revision Description", and "Checked By".  

If the columns have data in them, they would move to the next empty columns with those headers.  

So in the example, if AU, AV and AX on the "Title_Frame_Register sheet has data the copy of B, C and D from the "Revisions" sheet, the macro will move to the AZ, BA and BB of the "Title_Frame_Register Sheet.

And so on.
aikimarkCommented:
If the columns have data in them
1. Is that for the AU:AX block or on a row-by-row basis?
2. I see multiple revision columns.  Does the copy need to always place copied data into AU/AZ or to the rightmost unused revision columns?
regsampAuthor Commented:
1.  Yes, it is for the AU:AX block and I think I get your question, yes the rows should match with each other with corresponding information of Column B, the "Layout Tab" for the three columns and then once those columns have data copied over to them then the code would know for the next copy to go to the AZ/BB block and so on.

2.  The copy will always go to the next block that has empty columns with the Headers "Revision Date", "Revision Description", and "Checked By" until there is no more.
aikimarkCommented:
You will need to delete your hidden AW column in order for this code to work properly.  Alternatively, you can move it to the far right side of your Title_Frame_Register worksheet.
Sub Q_28727859()
    Dim wksSrc As Worksheet, wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Const cMinRevCol As Long = 47
    
    Set wksTgt = Sheets("Title_Frame_Register")
    Set wksSrc = Sheets("Revisions")
    
    Application.ScreenUpdating = False
    Set rngSrc = wksSrc.Range("B5")
    Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
    Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
    For Each rng In rngSrc
        Set rngTgt = wksTgt.Cells(rng.Row, wksTgt.Columns.Count).End(xlToLeft)
        If rngTgt.Column < cMinRevCol Then
            Set rngTgt = wksTgt.Cells(rng.Row, cMinRevCol)
        Else
            Set rngTgt = rngTgt.Offset(0, 4)
        End If
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = wksSrc.Range(rng, rng.Offset(0, 2)).Value
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
I see aikmark working on this with you already before i got back..i let him continue as i'm sure he has spent some time on this already and will come out a solution basis of your needs soon..
aikimarkCommented:
If you can't live without the hidden column AW in its current location, change line 20 to
            Set rngTgt = rngTgt.Offset(0, 4 + (5 - wksTgt.Range(rngTgt, rngTgt.Offset(0, 4)).Cells.SpecialCells(xlCellTypeVisible).Count))

Open in new window

aikimarkCommented:
Hang on.  Just ran it twice in a row and see that I need to make an adjustment.
regsampAuthor Commented:
Okay, can you add the new coding with the keeping the hidden the AW and below is what I got for results:
Revision.jpg
It is close but the rows were a little off and it did not do the Test 3 row?

Rev2.jpg
regsampAuthor Commented:
Here is a copy of the file again with the copy Macro I have in it just to make sure you have the newest one.
Title_Frame_Register-Master--3---4-.xls
aikimarkCommented:
I had to make sure the rngTgt pointed to a Revision Date column
Sub Q_28727859()
    Dim wksSrc As Worksheet, wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Const cMinRevCol As Long = 47
    
    Set wksTgt = Sheets("Title_Frame_Register")
    Set wksSrc = Sheets("Revisions")
    
    Application.ScreenUpdating = False
    Set rngSrc = wksSrc.Range("B5")
    Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
    Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
    For Each rng In rngSrc
        Set rngTgt = wksTgt.Cells(rng.Row, wksTgt.Columns.Count).End(xlToLeft)
        If rngTgt.Column < cMinRevCol Then
            Set rngTgt = wksTgt.Cells(rng.Row, cMinRevCol)
        Else
            'nudge rngTgt to the left until we are at a Revision Date column
            Do Until wksTgt.Cells(3, rngTgt.Column).Value = "Revision Date"
                Set rngTgt = rngTgt.Offset(0, -1)
            Loop
            Set rngTgt = rngTgt.Offset(0, 4 + (5 - wksTgt.Range(rngTgt, rngTgt.Offset(0, 4)).Cells.SpecialCells(xlCellTypeVisible).Count))
        End If
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = wksSrc.Range(rng, rng.Offset(0, 2)).Value
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

aikimarkCommented:
The data layout in the revisions worksheet is much different.  No wonder the rows don't align
regsampAuthor Commented:
I tried the new coding and got the error below on the line below:
Revision3.jpg
Revision4.jpg
regsampAuthor Commented:
"The data layout in the revisions worksheet is much different.  No wonder the rows don't align" Yes, you are right. I tried explaining the situation to the Manager but...
aikimarkCommented:
I don't doubt that you had an error.  The data layout is different.  In fact, the data in column A is missing in the workbook you most recently posted.

Which of the two data layouts will you be using?
regsampAuthor Commented:
Is there anyway to get it from this output below:
Rev5.jpg
to this output, accounting for what would be spaces I guess:
Rev6.jpg
regsampAuthor Commented:
If we can only use one layout, it would be the second image above please.
aikimarkCommented:
This works, but you will need to delete/move the AW column.
Option Explicit
Sub Q_28727859()
    Dim wksSrc As Worksheet, wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngFind As Range
    Const cMinRevCol As Long = 47
    
    Set wksTgt = Sheets("Title_Frame_Register")
    Set wksSrc = Sheets("Revisions")
    
    Application.ScreenUpdating = False
    Set rngSrc = wksSrc.Range("B3")
    Set rngSrc = wksSrc.Range(rngSrc, wksSrc.Cells(wksSrc.Rows.Count, 2).End(xlUp))
    Set rngSrc = rngSrc.Cells.SpecialCells(xlCellTypeConstants)
    For Each rng In rngSrc
        Do      '<<<GROUP
        
        Set rngFind = wksTgt.Columns(2).Find(rng.Offset(0, -1).Value)
        If rngFind Is Nothing Then
            Exit Do
        End If
        Set rngTgt = wksTgt.Cells(rngFind.Row, wksTgt.Columns.Count).End(xlToLeft)
        If rngTgt.Column < cMinRevCol Then
            Set rngTgt = wksTgt.Cells(rngFind.Row, cMinRevCol)
        Else
            'nudge rngTgt to the left until we are at a Revision Date column
            Do Until wksTgt.Cells(3, rngTgt.Column).Value = "Revision Date"
                Set rngTgt = rngTgt.Offset(0, -1)
            Loop
            Set rngTgt = rngTgt.Offset(0, 4)
        End If
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = wksSrc.Range(rng, rng.Offset(0, 2)).Value
        
        Loop While False        '>>>GROUP
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

For an explanation about the Do group, please read my article on the subject:
http://rdsrc.us/VCFFaB

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
regsampAuthor Commented:
That is looking great! Let me run it through a few more tests but it looks amazing. I don't know how you guys do it.  I deleted the AW column. If we/the manager needs it, they can add it at the end. Let me test and then look at your explanation.

I will update shortly but so far looks amazing. Thank you greatly in advance!
aikimarkCommented:
You can move it to the left of AU, but the cMinRevCol value will need to be adjusted.
regsampAuthor Commented:
I just deleted the hidden column. They can move it to the end like you said. I am just doing a little more testing but it is looking very good.
regsampAuthor Commented:
Just a quick follow up. On the clear button, if I wanted to clear the B, C, and D columns(Below 3) on the Revisions page, how could I alter this?:

Sub Clear()
Range("A3", Range("A3").End(xlDown)).Clear
End Sub
aikimarkCommented:
Sub Clear()
    ActiveSheet.Range(ActiveSheet.Range("A3"), ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
End Sub

Open in new window

regsampAuthor Commented:
Thank you again!  Great work.
regsampAuthor Commented:
Through some testing, I am getting different results. Sometimes I get an error message and then sometimes the data jumps?

Untitled.jpg
aikimarkCommented:
Although I wouldn't expect to see revision data on the row shown in the image file you posted, I am unable to recreated your errors from an image file.  Please post your workbook and comment on how to recreate the errors.
regsampAuthor Commented:
I think I got it. (Column A In the Revisions sheet) has to be filled with data when the Revision button is pressed in order for the layout to work correctly, right?

A user did it without having data in Column A and that was the result.
Title_Frame_Register-Master--3---4-.xls
aikimarkCommented:
That is correct.  Without a relationship between the source row and the target row, the code is either lost, wrong, or broken.

In your original workbook, the rows were aligned.
In your second workbook uploaded, the column A values were used to find the target row.
regsampAuthor Commented:
Okay, got it. Thank you very much again.
regsampAuthor Commented:
We have done some initial testing and it looks good from what I have seen. Thank you again.
regsampAuthor Commented:
The expert provided a lot of help and was very helpful.
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
VB Script

From novice to tech pro — start learning today.