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

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/

.

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/

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

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/

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/

.

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/

.