Complete an Excel Sheet from Address Data taken from rows (lines)

**trippyd** · 08-20-2019, 04:08 AM

Good morning everyone, thank you for the welcome to this support forum.

I thought that I was reasonably OK with Excel and basics - at this time however I am stumped. Here's the scenario. I am trying to output a 'Engineer Worksheet' (Form? Printable page)

1. I have a straight forward sheet which going left to right across the sheet has 'Name, Address, Tel, etc.

2. I have a second sheet which represents an Engineers Job Sheet - Which I would like to complete from the data entered from point "1" (name, address, tel, etc,), then a button to print the document.

Now of course I could use the "=" option from sheet 1 to sheet 2, this however is only good for one left to right line of the data - I would need to manually change it every time - which of course is pointless.

I need to enter the data in to sheet one as it needs to be a maintained for historic reasons.

Can you help please?

Kind regards
Dave

**Olly** · 08-20-2019, 04:23 AM

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**trippyd** · 08-20-2019, 05:06 AM

Hello Olly,

Hopefully file is attached.

Sheet 1 has the data - two line of it.

Sheet 2 has an engineers page I wish to populate and wish to print.

If I have four lines of data for example, I need it to populate the sheet 2 document four times and be able to print it with a print button.

Hope this helps and thank you once more for your help.

Kind regards
Dave

**trippyd** · 08-20-2019, 05:06 AM

Not sure what happened to file extension there! It's Excel 365.

The target goal is to complete the NAME and ADDRESS parts of the sheet.

If I can see how to complete that - I should be able to complete any other parts of it.

Best regards,
Dave

**Olly** · 08-20-2019, 06:27 AM

Don't quite understand the workflow you're trying to achieve.

Do you want to enter a unique identifier (e.g. Job No) on the 'Engineer Sheet', then retrieve the relevant details from 'Job Data' ? If this is the case, then which cell will be changed?

Or do you want to loop through each row in 'Job Data', and print an 'Engineer Sheet' for each row?

**trippyd** · 08-20-2019, 06:41 AM

Sorry Olly if not so clear. I will try to explain.

Sheet one holds as you can see Name, Address, general data across the cells. This data represents one job. Each line represents a job (line 2, 3, 4, 5 and so on)

I would like to transfer that data from sheet one, in to the relevant matching cell location of sheet two, which is the engineer sheet. I started with using the "=" option... that was OK until I reached line two :-(

So my goal is, take the information that I manually enter on a line by line basis on sheet 1. Then automatically get it to complete the engineers job sheet - on sheet 2.

Then to be able to print it.

I hope this helps.

I am so grateful for your time.

Kind regards
Dave

**Olly** · 08-20-2019, 06:46 AM

That didn't answer my question in post #5...

I fully understand you want to display one row of 'Job Data' data in the 'Engineer Sheet'. My question is about the trigger to select the row. Is it manually selected (and if so, how - which field is entered to identify the Job Data row), or do you want to automatically loop through all Job Data rows, and print an Engineer Sheet for all of them?

**trippyd** · 08-20-2019, 06:49 AM

Sorry,

Sheet one - is manually entered data.
Sheet two - data is automatically pulled in to it from sheet one.

Then as you say I would like to you to automatically loop through all Job Data rows, and print an Engineer Sheet for all of them when selected, which a print button.

(gosh - i feel so daft at times)

Thank you Olly,

Dave

**Olly** · 08-20-2019, 07:50 AM

Here's one approach:

Sub PrintAllEngineerSheets()
    Dim wsSrc As Worksheet
    Dim wsTgt As Worksheet
    Dim vSrc()
    Dim vtgt()
    Dim lRow As Long
    Dim i As Integer
    
    On Error GoTo Terminate
    
    Set wsSrc = Worksheets("Job Data")
    Set wsTgt = Worksheets("Engineer Sheet")
    
    'Extend ranges as required - keep in order, to map from Src to Tgt
    vSrc = Array("C1", "D1", "E1", "F1")
    vtgt = Array("B7", "B8", "A9", "H11")
    
    With wsSrc
        For lRow = 1 To .Cells(Rows.Count, 1).End(xlUp).Row - 1
            For i = LBound(vSrc) To UBound(vSrc)
                wsTgt.Range(vtgt(i)).Value = wsSrc.Range(vSrc(i)).Offset(lRow, 0).Value
            Next i
            
            wsTgt.PrintPreview  'change to wsTgt.PrintOut to print directly without preview
            
            For i = LBound(vtgt) To UBound(vtgt)
                wsTgt.Range(vtgt(i)).MergeArea.ClearContents
            Next i
        Next lRow
    End With
    
Terminate:
    If Err Then
        Debug.Print "Error", Err.Number, Err.Description
        Err.Clear
    End If
End Sub

**trippyd** · 08-20-2019, 08:57 AM

Hello Olly,

Great Thank you. After copying code in to master sheet it works a treat - always a however.

Is there a way you can add to the script so as I can select what "Lines" I wish to print?

At this time it wants to print 568 lines :-)

Thank you.

Regards
Dave

**Olly** · 08-20-2019, 09:17 AM

How will you be selecting which rows you want to print?

**trippyd** · 08-20-2019, 09:31 AM

Hello Olly,

I can either highlight or filter, print by date.

I can use whatever method is easier for you to compile, either way I am extremely grateful.

Regards
Dave

**trippyd** · 08-20-2019, 02:40 PM

Hello again Olly,

Hope you have had a great day.

The code you sent over does the first part perfectly. It completes the second sheet as needed.

I have tried since you sent this over to use various pieces of code found on line to complete the task.... but have failed miserably.

If you get an opportunity to help me with the last part I would be exceptionally grateful.

Kindest regards.
Dave

Complete an Excel Sheet from Address Data taken from rows (lines)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Re: Complete an Excel Sheet from Address Data taken from rows (lines)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need to find addresses from one address excel sheets to pull rows from the second sheet

Moving complete rows of data from one sheet to a new sheet when a date is entered

[SOLVED] VBA to Archive lines of data when work is complete

Repeating macro for various worksheets to complete lines in consolidation sheet

Linking Two Excel Sheets to Complete Data on One Sheet

Converting a complete, single column address into separate columns for ADDRESS, CITY, ST,

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions