PDA

View Full Version : Solved: copy specific cells sheet1 into form sheet2



mperrah
12-30-2006, 11:37 PM
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? :dunno
Mark

JimmyTheHand
12-31-2006, 12:05 AM
Hi Mark :hi:

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

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

Edit: Line break added by lucas for folks with small monitors.

Without any more details I can only suggest a very general code.

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

If it's not enough, then can you upload a small sample worksheet to show us what you want?

Jimmy

mperrah
12-31-2006, 12:18 AM
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
12-31-2006, 12:32 AM
the raw data has been reposted below tc2.xls

JimmyTheHand
12-31-2006, 12:34 AM
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.
Set Tgt = Sheets("Sheet2") 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
12-31-2006, 12:36 AM
This is the form i am populating
Only the top section pulls the info from the source

JimmyTheHand
12-31-2006, 12:39 AM
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
12-31-2006, 12:41 AM
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
12-31-2006, 12:51 AM
I attached the raw data and the form on the opposite reply tc is the data qac is the form

mperrah
12-31-2006, 12:52 AM
i cleaned up the raw data per your suggestion

JimmyTheHand
12-31-2006, 12:57 AM
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
12-31-2006, 01:04 AM
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
12-31-2006, 01:31 AM
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
12-31-2006, 02:02 AM
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.
Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Range("A1")
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
Tgt.Cells(x1, y1).Value = Src.Cells(RowTNM, ColTNM).Value 'TechName B4 is the same as
Tgt.Cells(0, 0).Value = Src.Cells(0, 0).Value 'TechName B4 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
12-31-2006, 02:25 AM
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
12-31-2006, 10:38 AM
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.

Sheets("Statement").PrintPreview 'Out

mperrah
12-31-2006, 10:33 PM
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
01-01-2007, 12:02 AM
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.:bow:
Mark
I'll paste the final

JimmyTheHand
01-01-2007, 01:19 AM
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 :D
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 :bow:

Jimmy

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

lucas
01-01-2007, 08:50 AM
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....

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



Steve, so much for an intrusion :D
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 :bow:

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. :friends:

mperrah
01-02-2007, 12:00 AM
Thanks Lucas,
the unloadme method works now. But now when I click just the first entry it stops (wont print or preview), though clicking any other entry works. It is not a big deal, now that you have solved the coding issues I can by-pass the preview altogether and go straight to print.
I do like the userform method options.
On the merge method, is there a way to make the EndID on the Main page update automaticaly after I paste the data on the Data page.
I was thinking a vlookup would work to scan Column b on the data sheet for the last entry or the first blank cell and return the column A value to the Main page B6 or named EndID.
I do have a working model, if you guys are sick of me I wouldn't blame you.
I appreciate all everyone has contributed.
Mark

mperrah
01-13-2007, 04:13 PM
I pulled this from a file you guys helped me with that puts the data on the form to print based on if I put anything in column A (an x for example)
where I change D3 with a formula, can I have it use a source from another sheet. I want to use the list page and check the row to pull three columns of data "Name" Number" "Radio" and the "date" form a static cell

Sub PrintFiles()
Dim cel As Range
Dim wb As Workbook
For Each cel In Range(Cells(3, 6), Cells(Rows.Count, 1).End(xlUp))
If cel.Offset(, 1) <> "" Then
Set wb = Workbooks.Open(cel)
wb.Sheets(1).Range("D3") = Now() + "1"
'tgt.sheets.cells("A5"=first row of checked data "name"
'tgt.technumber=src first list technumber
'etc then loop through till all checked rows have printed filled form
wb.Sheets(1).PrintPreview
wb.Close False
End If
Next
End Sub

does this get close?
Mark

mperrah
01-14-2007, 08:24 PM
I started thread 10877 to work in another direction. This method works for what we started, but as I saw more options I like where lucas is going with the new thread. I'll post the working model for this thread.
Thanks to all.
Mark

mperrah
04-01-2007, 01:15 AM
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.


Jimmy,
I have modified this document a bit and its working great for printing. Now my boss asked to limit paper storage, and if you can help me make a new worksheet for each row of data would work great.
I plane to update and save the file each week (starting with an empty slate weekly) so the size is not an issue (typacly 45 worksheets at most)

I'll past the file as it is. There are three worksheets "data" "staement" and "raw". I paste the raw data to the raw page, run the "modify" macro which trims the columns and pastes the result to "data" then check the column "A" to pick files rows to print and click the print macro comand button at top.

Can a macro generate new worksheets based on the "statement" worksheet using the data and keep them in this workbook?

Thanks for your help,
Mark