Solved: copy specific cells sheet1 into form sheet2

[mperrah]

Hello world,
I have a sheet with column headers and seperate entries in each row.

I need specific cells from each row copied into specific cells of a generated form on another worksheet to be printed. The end result will be each row of data will be transfered to the form and printed.

The key info is the name, address, phone, from the raw data. I need to loop through the rows and copy the info to the form. each row will be put in it's own form... ie sheet 1 is raw data, pertinent data from row 1 data gets inserted into the specific cells of the form on sheet2, row 2 data gets put into form on sheet3, etc until all rows have there own form with data filled in from sheet 1

I can make a seperate sheet for the form for each row of data and use formulas to pull the data from sheet 1, but the formula would have to be altered for each sheet to look at the next row downm on sheet 1. I know this kind of process can be automated in vb.

I am still new to coding but I think a for next loop and a cell= reference would work.

i looked thorugh the forum for data transfer, copy to, invoice many evtries are close but getting pieces of specific columns to make a form for each row I could not find...

any takers?
Mark

[JimmyTheHand]

Hi Mark

Referencing specific cells in VBA has several methods.
What I recommend to use based on your description of the task is

[vba]Sheets("Target").Cells(RowIndex1, ColumnIndex1) _
.Value = Sheets("Source").Cells(RowIndex2, ColumnIndex2).Value

Edit: Line break added by lucas for folks with small monitors.
[/vba]
Without any more details I can only suggest a very general code.

[vba]Dim i as Long, StartRow as Long, EndRow as Long
Dim RowN as Long
Dim RowA as Long
Dim RowP as Long
Dim ColN as Long
Dim ColA as Long
Dim ColP as Long
Dim Src as Worksheet, Tgt as Worksheet

Set Src = Activesheet
For i = StartRow to EndRow
Worksheets.Add
Set Tgt = Activesheet
Tgt.Cells(x1, y1).Value = Src.Cells(RowN, ColN).Value 'Name
Tgt.Cells(x2, y2).Value = Src.Cells(RowA, ColA).Value 'Address
Tgt.Cells(x3, y3).Value = Src.Cells(RowP, ColP).Value 'Phone
Activesheet.PrintOut
Next i
[/vba]
If it's not enough, then can you upload a small sample worksheet to show us what you want?

Jimmy

[mperrah]

I see how the data copying will work to a new added worksheet.
Is there a way to populate an existing worksheet (the blank form)
I can have a sheet2 as the form with the info cells blank and populate and print with each iteration of i ?

[mperrah]

the raw data has been reposted below tc2.xls

[JimmyTheHand]

Is there a way to populate an existing worksheet (the blank form)
I can have a sheet2 as the form with the info cells blank and populate and print with each iteration of i ?

Yes.
You need to know the name of the existing sheet, and use e.g.
[vba]Set Tgt = Sheets("Sheet2")[/vba] I might be wrong but I understood that you want to create a new sheet for each record, and keep the sheets after printout.
If you use the same single sheet for printing out all records, (i.e. take firs record, update sheet with data, print it out, then go to next record, update sheet, print out, next record, update sheet, etc.) then you should move the code line above outside/before the For...Next loop

[mperrah]

This is the form i am populating
Only the top section pulls the info from the source

[JimmyTheHand]

I think you should remove the attachment, because broadcasting people's personal data (IMO) is not recommended. Replace it with another that contains 3-4 rows of dummy data. More important would be to know what the printout form looks like.

[mperrah]

After I print the form I dont necessarily need to save it. It would be nice, but if the code is more difficult I can live without the perminent copy. The form will be used to perform a qc on dish network intallation jobs and the rest is paper based not electronic. this form is only needed to print the job the tech needs to roll on to qc with the pertinent info filled in.
I can use the raw data sheet with a macro to remove the columns I dont need to store a hard copy of all the data on one page.

[mperrah]

I attached the raw data and the form on the opposite reply tc is the data qac is the form

[mperrah]

i cleaned up the raw data per your suggestion

[JimmyTheHand]

Creating a new sheet for each person and keeping them is not difficult at all. I just see no point in it, as long as there's no further data processing done on those sheets. It's no use keeping the sheets just because they look nice. Two reasons come into mind:
1) Keeping 83 (or maybe more) sheets will result in a big increase in filesize.
2) Searching through that many sheets looking for one particular person will be quite a burden, I think.
Of course, you can save those personal sheets in separate files, that may be a better solution. But I still don't see why? You can always re-create them again, as needed, based on a template sheet and the data recors.

I'll be back soon with something I could imagine for this task.

EDIT:
I can see the new tc2.xls is full of dummy data. Good. But you should remove tc.xls from the original post. Use the EDIT button on the bottom of that post, then go advanced and manage attachments.

[mperrah]

I agree with you, I dont need to save the forms filled in
i can pull the raw data and send it through if i need a new form
and i can make a macro to tweak the data on one sheet for archiving.
Mark

[mperrah]

do you think this is close?
are the x1, y1 values the coordiates from the source or the target?
I put the cell location for the target at the end of the lines,
I'll scan the raw data for the source locations


Dim i As Long, StartRow As Long, EndRow As Long
Dim RowTNM As Long ' do i need this line for every row i am copying there are 12 cells needed
Dim RowTNB As Long
Dim RowINS As Long
Dim RowACT As Long
Dim RowWOT As Long
Dim RowN As Long
Dim RowA As Long
Dim RowP As Long
Dim RowC As Long
Dim RowS As Long
Dim RowZ As Long
Dim RowM As Long
Dim ColTNM As Long ' do i need this for every column?
Dim ColTNB As Long
Dim ColINS As Long
Dim ColACT As Long
Dim ColWOT As Long
Dim ColN As Long
Dim ColA As Long
Dim ColP As Long
Dim ColC As Long
Dim ColS As Long
Dim ColZ As Long
Dim ColM As Long

Dim Src As Worksheet, Tgt As Worksheet

Set Src = Sheets("Sheet1")
Set Tgt = Sheets("Sheet2")
For i = StartRow To EndRow
Tgt.Cells(x1, y1).Value = Src.Cells(RowTNM, ColTNM).Value 'TechName B4
Tgt.Cells(x2, y2).Value = Src.Cells(RowTNB, ColTNB).Value 'TechNumber B5
Tgt.Cells(x3, y3).Value = Src.Cells(RowINS, ColINS).Value 'Install date F4
Tgt.Cells(x4, y4).Value = Src.Cells(RowACT, ColACT).Value 'Account number D6
Tgt.Cells(x5, y5).Value = Src.Cells(RowWOT, ColWOT).Value 'wo type D7
Tgt.Cells(x6, y6).Value = Src.Cells(RowN, ColN).Value 'Name B10
Tgt.Cells(x7, y7).Value = Src.Cells(RowA, ColA).Value 'Address B11
Tgt.Cells(x8, y8).Value = Src.Cells(RowP, ColP).Value 'Phone D10
Tgt.Cells(x9, y9).Value = Src.Cells(RowC, ColC).Value 'city B12
Tgt.Cells(x10, y10).Value = Src.Cells(RowS, ColS).Value 'state D12
Tgt.Cells(x11, y11).Value = Src.Cells(RowZ, ColZ).Value 'zip F12
Tgt.Cells(x12, y12).Value = Src.Cells(RowM, ColM).Value 'mdu F11

ActiveSheet.PrintOut
Next i

[JimmyTheHand]

Ummm... That's not exactly how I'd start.
The code I posted first was very general, because I didn't know any specifics. You, on the other hand, know the exact reference to source and target cells. So there's no need for so many variables. You can copy a cells value with direct referencing, e.g.
[VBA] Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Range("A1")[/VBA]
Also, the above example shows two possible ways of referring to cell A1.

Besides, you must give a value to those variables before using them. All numeric variables start with zero as value. So
[vba] Tgt.Cells(x1, y1).Value = Src.Cells(RowTNM, ColTNM).Value 'TechName B4[/vba] is the same as
[vba] Tgt.Cells(0, 0).Value = Src.Cells(0, 0).Value 'TechName B4[/vba] which will definitely go to error.

I've put together a small something, see the attachment.
Press alt+F8, then run the macro called "test".
All code is on the userfom's module, except the macro "test", that displays the userform.

Actually, how much programming experience do you have?
And what is it you would like, a working solution, or you need only some little help when you are stuck, but basically you want to do it yourself.
In latter case the attachment might be a spoiler

Jimmy

[mperrah]

A working example would be awesome and I think your sample can be tweaked for what I'm attempting. Im very new to coding, i came from actionscript in flash and html. I see how the code works and i'm learning the verbage but putting it together is challenging. I have posted a few questions here in the past and have had outstanding help every time. I'm really good with formulas but I'm reaching the boundaries of what they can do and I'm being nudged into VB (happily) I love what it can do, the more I learn, the more I learn I don't know
. Thank you for your patience.
Mark

[lucas]

Hi Guys,
I don't wish to intrude on the progress being made here. Jimmy is doing a great job but I have a file that I think Ken Puls contributed some time back that seems to address what your trying to do here.

Three sheets.
first to select the range you wish to work with on the data sheet and run the macro.
the data sheet contains your info
template(this is the one you need to look over)

On the template sheet look at the formula's. They are linked to the data sheet on row 2 only. Don't change the row number in the formula's only the column letter to refelect the data you wish to show in that location as the script will loop through the range you select and place it in row 2 for the print operation.

You can customize the template to suit your needs. even put a chart using the data that you get in the cells.

I only did a couple of them as I was not able to completely understand what you are trying to collect to print....

Also after you get it working the way you want you can change this line to printout instead of preview. I use it this way for testing only.
[vba]
Sheets("Statement").PrintPreview 'Out
[/vba]

[mperrah]

I think I can map the data to my form with this. Thank you
Also I used Jimmy's code with print preview instead of print out
and it locks up at print preview.
Glad I saved the changes before I tested the print.
I'll try Lucas' format and I'll post my results.
Thanks so much for your help. I Love this stuff.
Mark

[mperrah]

Lucas we got it,
I had problems at first. The targetID was looking for alphabetic order but once I passed 9 ID's, it went out of wack (in excel 10 comes after 1. So I put a leading 0 for 1 to 9 and it worked fine. I do like the user form Jimmy started so i can pick and choose what gets printed, but i can't figure out why it locks up.
Lucas' form works but if jimmy can debug the user form that would be hot too.
Thank you guys.
Mark
I'll paste the final

[JimmyTheHand]

Mark, if you set the userform's ShowModal property to False, it will at least allow you to close the print preview intead of freezing. However, even with multiple row selection (or Print all sheets option) you will get only one preview, and I'm not sure I can work around this. But since you've got another solution now, I guess this isn't important anymore, except as a curiosity

Steve, so much for an intrusion
If my intention had been to bask in glory by merits of my own code, now I would be properly humbled. Fortunately, my intention was to help, and I'm content that Mark got it. Still, this lesson about vanity and humbleness is worth keeping in mind. Thank's for teaching me

Jimmy

(PS: You wouldn't think that such higher levels of lessons are available in a technical forum... )

[lucas]

Mark, if you set the userform's ShowModal property to False, it will at least allow you to close the print preview intead of freezing. However, even with multiple row selection (or Print all sheets option) you will get only one preview, and I'm not sure I can work around this. But since you've got another solution now, I guess this isn't important anymore, except as a curiosity

Hi guys,
another option for dealing with the form during printpreview is to close the form just before the preview as shown in jimmy's code below....
[VBA]
Sub NewSheet(DRow As Long)
Dim Tgt As Worksheet
Dim Src As Worksheet
Set Src = ThisWorkbook.Sheets("Data")

Sheets("Template").Copy After:=Sheets(2)
Set Tgt = ActiveSheet
Tgt.Name = Src.Range("K" & DRow).Value

Tgt.Range("B10").Value = Src.Range("K" & DRow).Value
Tgt.Range("D10").Value = Src.Range("L" & DRow).Value
Tgt.Range("B11").Value = Src.Range("P" & DRow).Value
Unload Me
' Tgt.PrintOut
Tgt.PrintPreview
Application.DisplayAlerts = False
Tgt.Delete
Application.DisplayAlerts = True
Set Tgt = Nothing

End Sub
[/VBA]

Steve, so much for an intrusion
If my intention had been to bask in glory by merits of my own code, now I would be properly humbled. Fortunately, my intention was to help, and I'm content that Mark got it. Still, this lesson about vanity and humbleness is worth keeping in mind. Thank's for teaching me

Jimmy

Jimmy you are doing a great job! All I did was contribute a file that I had on hand that seemed to address the question in this thread. You are a much better code writer than I am and folks like you are very important to this forum! Let's continue to work together to find solutions.