Dim a value

D

daniroy

Hello there and thanks again for the group help
My VBA level is a shame:

I need to copy/paste data and the number of lines to copy on sheet
"Datas" is define in "Define" sheet on the cell C8.

I am not even able to declare C8 as a variable, first step before
asking to copy datas.
Up to now here is what I have done:
_____________________
Sub DefineWorkingDatas()

Sheets("Define").Select

Dim NbrofDays As Integer

MbrofDays = Range(C8).Value

End Sub
______________________

It is not working obviously I will appreciate any help believe me...

thanks
Daniel
 
D

Dave Peterson

You could use:

MbrofDays = Range("C8").Value

Or without the selecting:

Sub DefineWorkingDatas()
Dim NbrofDays As Long
MbrofDays = worksheets("define").Range("C8").Value
End Sub

I changed Integer to long--Longs are actually faster (less for the pc to do) and
can handle larger numbers.
 
D

daniroy

Thanks Dave,

This is working fine ... I am now trying to setup the area to copy ...
on sheet "Input Datas" (which is the correct name of the sheet in
facts), I now want to select area A2:BNbrofdays ... here is how looks
the code for that
Range("A2, B & NbrofDays").Select
but it is not working ...what should I write correctly ?
I know, I am a pain but thanks a lot!

and the full code is
___________
Sub DefineWorkingDatas()

Dim NbrofDays As Long

NbrofDays = Worksheets("Create Spread").Range("C8").Value

Worksheets("Input Datas").Select
Range("A2, B & NbrofDays").Select
Selection.Copy


End Sub
_________
 
D

Dave Peterson

You can avoid selecting stuff and just work with the ranges:


Sub DefineWorkingDatas()
Dim NbrofDays As Long
NbrofDays = Worksheets("Create Spread").Range("C8").Value
Worksheets("Input Datas").range("A2:B" & NbrofDays).copy
End Sub

And if you know where you want to paste it:

Sub DefineWorkingDatas()
Dim NbrofDays As Long
dim DestCell as range
NbrofDays = Worksheets("Create Spread").Range("C8").Value

set destcell = worksheets("otherworksheetnamehere").range("b99")

Worksheets("Input Datas").range("A2:B" & NbrofDays).copy _
destination:=destcell

End Sub

If you wanted to copy after the last used cell in column A of sheet99:

Sub DefineWorkingDatas()
Dim NbrofDays As Long
dim DestCell as range
NbrofDays = Worksheets("Create Spread").Range("C8").Value

with worksheets("sheet99")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

Worksheets("Input Datas").range("A2:B" & NbrofDays).copy _
destination:=destcell

End Sub
 
D

daniroy

thanks again, Dave, you are excellent, but I have a small problem at
the end of the code

Sub DefineWorkingDatas()

Dim NbrofDays As Long
Dim DestCell As Range

NbrofDays = Worksheets("Create Spread").Range("C8").Value

Set DestCell = Worksheets("Working Datas").Range("A2")

Worksheets("Input Datas").Range("A2:B" & NbrofDays).Copy
Destination = DestCell

End Sub


nothing is pasted, as code is missing for this ... And still I did try
a few things but without success

regards and many thanks again
Daniel
 
E

Earl Kiosterud

Daniel,

There is more than one issue here. What does "...not able to declare C8 as
a variable..." mean? You're attempting to declare NvrofDays as an integer
variable, which is fine (though conceivably, since it can only go to 32,768,
and a sheet can hold twice that many rows, it could eventually fail). What
is "Datas?"

You're trying to use a variable, MbrofDays, which isn't dimmed (unless
that's a typo in your post, and you meant NbrofDays). You're trying to use
a variable, C8, which is undefined (unless that's another typo, and you
meant Range("C8") in quotes).

You don't say what error message you get, or what does or doesn't happen
when you enter this code, or try to run it. You speak of copying a variable
number of rows, but there's no code for that.

You can copy/paste code directly to/from the VBE to a post.
 
D

Dave Peterson

You dropped a couple of characters...

Sub DefineWorkingDatas()

Dim NbrofDays As Long
Dim DestCell As Range

NbrofDays = Worksheets("Create Spread").Range("C8").Value

Set DestCell = Worksheets("Working Datas").Range("A2")

Worksheets("Input Datas").Range("A2:B" & NbrofDays).Copy _
Destination:=DestCell

End Sub

(Lost the line continuation character at the end of the .copy line and lost the
colon in the destination:=destcell line.)

And you're sure your data in C8 of "Create Spread" is what you want--and there
is data in "input datas" A2:B#?
 
Top