PDA

View Full Version : Solved: Copy cells from a excel form to different worksheets



Audax
03-29-2007, 01:33 PM
Hi guys
I have a workbook with 40 worksheets (1 thru 40), the worksheet number 1 is a form with information in 8 different cells (A3, B4, B8, C12, D5, D8, E9 and E13), the information in B4 refers to a worksheet number, Ex. If B4=21, refers to worksheet 21. I want to copy the information of the 7 other cells of worksheet 1 in 7 different cells of the worksheet referred B4 above starting with the column A first row empty and the rest of the numbers in columns adjacents B, C, D, E, F, G, H), of course if I change the value of B4, say to 30, then I expect the values on the other 7 cells copied to worksheet 30, and so on.
I'll really appreciate any answer
Thank a lot

acw
03-29-2007, 05:21 PM
Hi

how about


Sub aaa()
Dim OutSH As Worksheet, rng As Range, OutPL As Range
Set OutSH = Sheets(Format(Range("b4").Value, "@"))
Set rng = Union(Range("a3"), Range("B8"), Range("c12"), Range("d5"), Range("d8"), Range("e9"), Range("e13"))
Set OutPL = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
cntr = 0
For Each ce In rng
OutPL.Offset(0, cntr).Value = ce.Value
cntr = cntr + 1
Next ce
End Sub



Tony

Aussiebear
03-29-2007, 05:26 PM
G'day Audax,

Could you post an example of your workbook, so we can see what it is that you have in mind.

Also I uderstand that you are chasing the value of cell B4 to determine the sheet number to post the data to. Is therefore the value of A3 to go to column B, B8 to column c etc of the selected sheet?

Ted

Audax
03-30-2007, 06:15 AM
Thank guys for give me some input. The solution give by Tony work the first time when I run the macro, after that I receive the error message Run-time error 9...Subscript out of range. Here is an example.
Workbook = Voucher
Woksheet1= 1, Worksheet2=2, Worksheet3=3 and so on to Worksheet40=40
In worksheet 1 I have the following values Cell A3=2, B4=3, B8=6, C12=5, D5=9, D8=10, E9=13 and E13=7......Now, I want to copy A3, B8, C12, D5, D8, E9 and E13 in Worksheet 3 (because B4=3) then Worksheet 3 will have the following inputs after run the macro A1=2, B1=6, C1=5, D1=9, E1=10, F1=13 and G1=7, when I run the macro the second time I'll have the values in A2, B2, C2, D2, E2, F2 and G2, because is the first empty row, the third time I'll have the values in A3, B3...and so on.

Thank you again for your help

acw
04-01-2007, 03:13 PM
Hi

Can you check that the names are an exact match - no extra spaces or characters.

If you have one that works (say sheet2) can you rerun with the same info, or does it bomb out a second time with the same output sheet.


Tony

Audax
04-02-2007, 06:00 AM
Thanks ACW, It works 100%.

Thank a lot again