Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
I have excel table on excel VBA form. There are no. of cells filled an
need to export to excel sheet. I put a command button to do that & fo one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & m code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2 15).Value ....... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should hav values from Spreadsheet cells(1,15) to (40,15) and cells(1,16) t (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicappe with the knowledge -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
Try entering this in C1; = then click the reference cell
and press enter. Next, copy the formula down the number of required cells. Repeat for the other column. Don't bother with using code if that's okay. Regards, GS -----Original Message----- I have excel table on excel VBA form. There are no. of cells filled and need to export to excel sheet. I put a command button to do that & for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ....... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should have values from Spreadsheet cells(1,15) to (40,15) and cells (1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
Dim rng1 as Range
Dim rng2 as Range set rng1 = Worksheets("Sheet1").Range("C1").Value set rng2 = Worksheets("Sheet1").Range("F1").Value rng1.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 15).Resize(1,40).Value rng2.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 16).Resize(1,40).Value -- Regards, Tom, Ogilvy "Bhuktar S " wrote in message ... I have excel table on excel VBA form. There are no. of cells filled and need to export to excel sheet. I put a command button to do that & for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ...... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should have values from Spreadsheet cells(1,15) to (40,15) and cells(1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
Dear Tom,
I get Runtime error '438' and message"Object doesn't support this property or method" and when clocked debug, highlights code rng1.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 15).Resize(1,40).Value It means, I think, Resize method cannot be used for Spreadsheet contro of the form. The Spreadsheet control is Microsoft Office Spreadsheet 9. obtained as additional control from the toobox. I am using Windows 2000 & Office 2000. Please suggest the solution. Tom Ogilvy wrote: *Dim rng1 as Range Dim rng2 as Range set rng1 = Worksheets("Sheet1").Range("C1").Value set rng2 = Worksheets("Sheet1").Range("F1").Value rng1.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 15).Resize(1,40).Value rng2.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 16).Resize(1,40).Value -- Regards, Tom, Ogilvy "Bhuktar S " wrote i message ... I have excel table on excel VBA form. There are no. of cells fille and need to export to excel sheet. I put a command button to do that for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in th worksheet. This has no problem, bus since there are lots of them to be copie & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ...... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These shoul have values from Spreadsheet cells(1,15) to (40,15) and cells(1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I a handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
I don't know anything about the spreadsheet control, but it you want to try
looping you can try this Dim rng1 as Range Dim rng2 as Range set rng1 = Worksheets("Sheet1").Range("C1").Value set rng2 = Worksheets("Sheet1").Range("F1").Value for i = 1 to 40 rng1(i).Value = _ Spreadsheet1.Cells(i, 15).Value rng2.(i).Value = _ Spreadsheet1.Cells(i, 16).Value Next -- Regards, Tom Ogilvy "Bhuktar S " wrote in message ... Dear Tom, I get Runtime error '438' and message"Object doesn't support this property or method" and when clocked debug, highlights code rng1.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 15).Resize(1,40).Value It means, I think, Resize method cannot be used for Spreadsheet control of the form. The Spreadsheet control is Microsoft Office Spreadsheet 9.0 obtained as additional control from the toobox. I am using Windows 2000 & Office 2000. Please suggest the solution. Tom Ogilvy wrote: *Dim rng1 as Range Dim rng2 as Range set rng1 = Worksheets("Sheet1").Range("C1").Value set rng2 = Worksheets("Sheet1").Range("F1").Value rng1.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 15).Resize(1,40).Value rng2.Resize(40,1).Value = _ Spreadsheet1.Cells(1, 16).Resize(1,40).Value -- Regards, Tom, Ogilvy "Bhuktar S " wrote in message ... I have excel table on excel VBA form. There are no. of cells filled and need to export to excel sheet. I put a command button to do that & for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ...... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should have values from Spreadsheet cells(1,15) to (40,15) and cells(1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ * --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
Hi Buktar,
If you entered a formula in cells C1-C40 and F1-F40 that referenced the source data, the respective cells in C and F would automatically update, showing the values as soon as they are entered in the referenced cells. Here's the formula to enter in C1: =Cells(1, 15) then copy it down the number of rows required. Repeat for F1 as follows: =Cells(1, 16) Regards, GS -----Original Message----- I have excel table on excel VBA form. There are no. of cells filled and need to export to excel sheet. I put a command button to do that & for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ....... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should have values from Spreadsheet cells(1,15) to (40,15) and cells (1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy large data from Form to Excel
Sorry, I left out a reference to the sheet in the formula.
Put the sheet name and ! between = and Cells(... without any spaces. This will ensure the formula is referencing cells on the correct sheet GS -----Original Message----- I have excel table on excel VBA form. There are no. of cells filled and need to export to excel sheet. I put a command button to do that & for one cell I put the code as : Private Sub CommandButton1_Click() Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value End Sub i.e. after I click the button, the value is copied in the worksheet. This has no problem, bus since there are lots of them to be copied & my code will become very large, such as Worksheets("Sheet1").Range("C1").Value = Spreadsheet1.Cells(1, 15).Value Worksheets("Sheet1").Range("C2").Value = Spreadsheet1.Cells(2, 15).Value ....... and so on. The cells on worksheet are C1 to C40 and F1 to F40. These should have values from Spreadsheet cells(1,15) to (40,15) and cells (1,16) to (40,16) respectivley. How do I make it in simple way ?( may be looping ? but I am handicapped with the knowledge) --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you copy formulas only across a large sheet, under data? | Excel Worksheet Functions | |||
Copy/Paste Macro using large amount of Data | Excel Discussion (Misc queries) | |||
Automatically copy data from large worksheet into individual workb | Excel Worksheet Functions | |||
Data Form to large | Excel Discussion (Misc queries) | |||
Using data form in large mailing list spreadsheet | Excel Discussion (Misc queries) |