Moving Data from one sheet to another

[JohnF]

I have one sheet that users complete. the sheet contains varying rows of data and 7 columns.

The data could start on any row between 10 and 15 and will continue for a varying amount of rows, typically about 10 rows.

I want to copy the data from the user sheet to another master sheet when the user closes the user entry sheet.

The user sheet is a template and each time the user opens it appears as a new entry form.

the data in the master sheet should be added to each time the user completes and closes the template.

Can anyone help. Sorry about the description above I hope someone out there can decipher what I want. It is clear in my mind, But after reading the above I'm not sure if I have explained clearly

 

[tactps]

Post your sheets using the HTML Maker so that we can see what data they are inputting and where you want it to go.

It also sounds like you could use the Template Wizard from www.microsoft.com to do this for you.

Take a look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;148314

 

[JohnF]

Dont know how

dont know how to post my sheets but, I tried the template wizard good tool but, does not seem to work on a column of cells.

I have changed the user sheet so the starting row is row 10, each of the seven columns contain data, the number of rows depends on hoew many are entered by the user.

Once the user has completed his/her entering they save. This is when I want their completed cells copied to a master sheet.

When doing this the data should be copied to the next blank row so as not to delte the data already on this sheet.

Is their any way of doing this.

 

[tactps]

Where is the data that you want to copy (sheet name and cell references) and where do you want it copied to (bottom of which sheet name?)

 

[tactps]

Let's assume that the user always has to enter data into column A and the sheet is called "Sheet1". You want to copy it into column A in "Master"

1. Press Alt-F11 to bring up VBA
2. In the left pane, double-click "ThisWorkbook"
3. Paste this in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").select
if [a65536].end(xlup).row > 9 then
range("A10:A"&[a65536].end(xlup).row).entirerow.copy destination:=Sheets("Master").[a65536].end(xlup).offset(1,0)
End Sub

(Untested)

 

[JohnF]

That works, But

Thanks that works.

First it came up with an error regarding end if, so I put an End if before End sub.

Is it just a matter of extending the range to select all 10 columns of data?

The two worksheets are in separate workbooks with the first being a template, the other is a normal workbook that is used to collate all the data entered into the template in one place, do I just give the path of the master sheet in the macro? and can the master workbook remain closed?

Thanks
John F