VBA: Moving data from another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sillyhat@yahoo.com - 05 Jan 2008 20:54 GMT

Hello,

Can someone please help. I am trying to understand how VBA can be used
to get data from one sheet to another. I have some code that works but
I think it could be a bit better.

With regard to the code below, the queries I have are as follows:-

Dim XR
Why can't/shouldn't this be defined as a range?

Dim x
Should this be a variant?

XR = Sheets(WS1).Columns(FromCol)
Might be better to define a range since we have lastrow but how?

For Each x In XR
Why doesn't 'For Each x In Sheets(WS1).Columns(FromCol)' work,
since XR was set to be Sheets(WS1).Columns(FromCol) anyway!

Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
Why don't these work:
YR.Cells(i, ToCol).Value = x^2
YR.Row(i).Value = x^2
What variations that references YR would work?

Thanks for all constructive advice given.

Hal

Code follows....
'---------8<---------8<---------8<---------8<---------8<---------8<---------
'Sheet1 has a list of numbers in column "A"
'----------------------------------------------------------
Sub GenData()
Call GenSquares("Sheet1", "A", "Sheet2", "D")
End Sub
'----------------------------------------------------------

Private Sub GenSquares(WS1, FromCol, WS2, ToCol)
Dim XR
Dim YR
Dim x
Dim i As Integer
Dim lastrow As Integer

lastrow = Sheets(WS1).UsedRange.Rows.Count

XR = Sheets(WS1).Columns(FromCol)
YR = Sheets(WS2).Columns(ToCol)

i = 1
For Each x In XR
If i > lastrow Then Exit For
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
i = i + 1
Next x
End Sub
'----------------------------------------------------------
'---------8<---------8<---------8<---------8<---------8<---------8<---------

Reply to this Message

Jim Cone - 05 Jan 2008 22:51 GMT

To establish a range, you have to use the Set statement.
However in a For/Each statement Excel sets the range, the "x" in the loop.
This works...
'--
Private Sub GenSquares(WS1 As Worksheet, FromCol As Long, _
WS2 As Worksheet, ToCol As Long)
Dim XR As Range
Dim YR As Range
Dim x As Range
Dim i As Long
Dim lastrow As Long

lastrow = WS1.UsedRange.Rows.Count
Set XR = WS1.Columns(FromCol)
Set YR = WS2.Columns(ToCol)

i = 1
For Each x In XR.Cells
If i > lastrow Then Exit For
WS2.Cells(i, ToCol).Value = x.Value ^ 2
i = i + 1
Next x
End Sub
'--
Sub GetStartedWithSquares()
Call GenSquares(Worksheets(1), 2, Worksheets(2), 2)
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<sillyhat@yahoo.com>
wrote in message
Hello,
Can someone please help. I am trying to understand how VBA can be used
to get data from one sheet to another. I have some code that works but
I think it could be a bit better.

With regard to the code below, the queries I have are as follows:-

Dim XR
Why can't/shouldn't this be defined as a range?

Dim x
Should this be a variant?

XR = Sheets(WS1).Columns(FromCol)
Might be better to define a range since we have lastrow but how?

For Each x In XR
Why doesn't 'For Each x In Sheets(WS1).Columns(FromCol)' work,
since XR was set to be Sheets(WS1).Columns(FromCol) anyway!

Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
Why don't these work:
YR.Cells(i, ToCol).Value = x^2
YR.Row(i).Value = x^2
What variations that references YR would work?

Thanks for all constructive advice given.

Hal

Code follows....
'---------8<---------8<---------8<---------8<---------8<---------8<---------
'Sheet1 has a list of numbers in column "A"
'----------------------------------------------------------
Sub GenData()
Call GenSquares("Sheet1", "A", "Sheet2", "D")
End Sub
'----------------------------------------------------------

Private Sub GenSquares(WS1, FromCol, WS2, ToCol)
Dim XR
Dim YR
Dim x
Dim i As Integer
Dim lastrow As Integer

lastrow = Sheets(WS1).UsedRange.Rows.Count

XR = Sheets(WS1).Columns(FromCol)
YR = Sheets(WS2).Columns(ToCol)

i = 1
For Each x In XR
If i > lastrow Then Exit For
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
i = i + 1
Next x
End Sub
'----------------------------------------------------------
'---------8<---------8<---------8<---------8<---------8<---------8<---------

Reply to this Message

sillyhat@yahoo.com - 06 Jan 2008 15:50 GMT

Nice one Jim,

Thats very useful.

I also notice that with your code, YR can be used so that instead of
WS2.Cells(i, ToCol).Value = x.Value ^ 2
either of the following work
YR.Rows(x.Row) = x.Value ^ 2
YR.Rows(i) = x.Value ^ 2

Thanks a lot.
Hal
~~~~~~~~~~~~~~

> To establish a range, you have to use the Set statement.
> However in a For/Each statement Excel sets the range, the "x" in the loop.
[quoted text clipped - 90 lines]
> '----------------------------------------------------------
> '---------8<---------8<---------8<---------8<---------8<---------8<---------

Reply to this Message