different workbooks

[Guest]

i have :

Sub inputdata()

Set destSheet = Worksheet("data")
Set sourceSheet = Worksheet("register")
Workbooks("data.xls").Activate
DestSheet(Range("a1:a10")).Value = sourceSheet(Range("a1:a10")).Value


my question/problem is that destsheet and sourcesheet are in different
workbooks...how would i add that in my code? i tried some stuff but couldnt
get it
the 2 workbooks are data.xls and register.xls
thanks in advance

 

[Guest]

Hi.
Are the 2 books open at that time? I assume they are.
Replace the two 'Set' lnes by:
Set destSheet = Workbooks("data.xls").Worksheet("data")
Set sourceSheet = Workbooks("register.xls").Worksheet("register")

Regards,
Sebastien

 

[Norman Jones]

Hi Choice,

Try:

Sub InputData()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim SourceSheet As Worksheet
Dim DestSheet As Worksheet

Set WB1 = Workbooks("Data.xls")
Set WB2 = Workbooks("Register.xls")
Set SourceSheet = WB1.Sheets("Data")
Set DestSheet = WB2.Sheets("Register")

SourceSheet.Range("A1:A10").Copy
DestSheet.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

 

[Guest]

cant seem to get it to work

Hi.
Are the 2 books open at that time? I assume they are.
Replace the two 'Set' lnes by:
Set destSheet = Workbooks("data.xls").Worksheet("data")
Set sourceSheet = Workbooks("register.xls").Worksheet("register")

Regards,
Sebastien

 

[Norman Jones]

Hi Choice,

Sebastien's code worked for me providing each instance of :

Worksheet

was reolaced with

Worksheets

 

[Norman Jones]

Hi Choice,

I omitted to say that, in order successfully to use Sebastien's code, you
would need to correct your code line:

DestSheet(Range("a1:a10")).Value =
sourceSheet(Range("a1:a10")).Value

to read:

DestSheet.Range("a1:a10").Value = _
SourceSheet.Range("a1:a10").Value

 

[Norman Jones]

Hi Checker,

it worked OK with me without the alteration.

I am sorry to say that this assertion is even less accurate than your
chronmetrical skills.

However, given that the code works for you, perhaps *you* would care to
explain to Choice why it is that it he "cant seem to get it to work".

 

[Norman Jones]

Typo!

chronmetrical was intended to be chronometrical.

 

[Patrick Molloy]

Set destSheet = Workbooks("data.xls").Worksheets("data")
Set sourceSheet = Workbooks("register.xls").Worksheets("register")
DestSheet.Range("a1:a10").Value = sourceSheet.Range("a1:a10").Value

 

[Cheker]

it worked OK with me without the alteration.

Hi Choice,

I omitted to say that, in order successfully to use Sebastien's code, you
would need to correct your code line:


to read:

DestSheet.Range("a1:a10").Value = _
SourceSheet.Range("a1:a10").Value

 

[Cheker]

Hi Norman,
I do not know why it didn't work for choice.
All I did was to try it as it was...It just worked fine.

 

[Guest]

WORKED...thank you

Next question has a couple parts.
so i have,
Set destSheet = Workbooks("data.xls").Worksheets("data")
Set sourceSheet = Workbooks("register.xls").Worksheets("register")
DestSheet.Range("a1:a10").Value = sourceSheet.Range("a1:a10").Value

but on destsheet...how can i make it go to the bottom of the entries instead
of a set range.
i have: cells(rows.count,1).End(xlup)(2).Select, but im not sure how to
incorporate both.

next part of question, how can i make data.xls hidden? and if so...can it be
shared?

thank you in advance

 

[Guest]

i need the opposite of that...
i need the beginning of the range to be the last value in the column

example:
on data.xls column A, there are 25 entries already, i need the range to
start on the 26th cell and start the range from there on.

thank you