Copy large data from Form to Excel

[Bhuktar S]

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

 

[Garry]

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

 

[Tom Ogilvy]

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

 

[Bhuktar S]

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]

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

 

[Garry]

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

 

[Garry]

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