VB Macro for Excel
 

Hi all, i've a little problem.
i've to copy data from one file to its updated version.
let's say:
My.T.y.M.xls is the new file (the one to be updated)
My.T.y.M2.xls is the old file (from which i want to copy data)

how is it possible to copy data recorded in non-adiacent cell in a
quick way???

At the moment i've written a macro that works fine but it's not written
in the best way, can someone help me please ??

i would like to avoid the macro to jump from one file to the other one
changing program lines such as (see macro list below):

Windows("My.T.y.M2.xls").Activate
copy cell content
Windows("My.T.y.M2.xls").Activate
paste cell content

with a more compact instruction.

below you've the macro i use.

Sub copiaform_da_MytyM2_a_MytyM()
'
' copiaform_da_MytyM2_a_MytyM Macro
' Macro registrata il 19/10/2005 da fulvio
Dim i As Integer

For i = 15 To 123 Step 12

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("B" & i).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("B" & i).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("C" & i + 1 & ":C" & i + 11).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("C" & i + 1 & ":C" & i + 11).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("E" & i + 1 & ":E" & i + 11).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("E" & i + 1 & ":E" & i + 11).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next i
Range("A1").Select
Sheets("My T.y.M's infos").Select
Range("R197").Select

End Sub

thanks for your help

VB Macro for Excel
 

You can tighten this up with worksheet objects. One for the current book and
one for the old book

dim wksCopyTo as worksheet
dim wksCopyFrom as worksheet

set wkscopyto = thisworkbook.sheets("saved line-ups")
set wkscopyfrom = workbooks("My.T.y.M2.xls").sheets("saved line-ups")

For i = 15 To 123 Step 12
wkscopyfrom.Range("B" & i).copy
wkscopyto.Range("B" & i).PasteSpecial Paste:=xlPasteValues
'...

next i
set wkscopyfrom = nothing
set wkscopyto = nothing

Don't use the selects. There is no need with the worksheet objects. They can
actually be very problematic...
--
HTH...

Jim Thomlinson


"furbiuzzu" wrote:

Hi all, i've a little problem.
i've to copy data from one file to its updated version.
let's say:
My.T.y.M.xls is the new file (the one to be updated)
My.T.y.M2.xls is the old file (from which i want to copy data)

how is it possible to copy data recorded in non-adiacent cell in a
quick way???

At the moment i've written a macro that works fine but it's not written
in the best way, can someone help me please ??

i would like to avoid the macro to jump from one file to the other one
changing program lines such as (see macro list below):

Windows("My.T.y.M2.xls").Activate
copy cell content
Windows("My.T.y.M2.xls").Activate
paste cell content

with a more compact instruction.

below you've the macro i use.

Sub copiaform_da_MytyM2_a_MytyM()
'
' copiaform_da_MytyM2_a_MytyM Macro
' Macro registrata il 19/10/2005 da fulvio
Dim i As Integer

For i = 15 To 123 Step 12

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("B" & i).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("B" & i).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("C" & i + 1 & ":C" & i + 11).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("C" & i + 1 & ":C" & i + 11).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Windows("My.T.y.M2.xls").Activate
Sheets("saved line-ups").Select
Range("E" & i + 1 & ":E" & i + 11).Select
Application.CutCopyMode = False
selection.Copy

Windows("My.T.y.M.xls").Activate
Sheets("saved line-ups").Select
Range("E" & i + 1 & ":E" & i + 11).Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next i
Range("A1").Select
Sheets("My T.y.M's infos").Select
Range("R197").Select

End Sub

thanks for your help

VB Macro for Excel
 

thanks for your solution, i'll try it, but isn't it possible to write
something really compact as that ??

For i = 15 To 123 Step 12

Windows("MyTyM").worksheets("saved line-ups").Range("B" & i).value =
Windows("MyTyM2").worksheets("saved line-ups").Range("B" & i).value

next i

etc.. etc

or instead of "Windows" some other word that indicates source &
destination file ??

VB Macro for Excel
 

You bet. I was following your original outline. If you are only moving 1 cell
value at a time then...

dim wksCopyTo as worksheet
dim wksCopyFrom as worksheet

set wkscopyto = thisworkbook.sheets("saved line-ups")
set wkscopyfrom = workbooks("My.T.y.M2.xls").sheets("saved line-ups")

For i = 15 To 123 Step 12
wkscopyto.Range("B" & i).Value = wkscopyfrom.Range("B" & i).Value
'...

next i
set wkscopyfrom = nothing
set wkscopyto = nothing


--
HTH...

Jim Thomlinson


"furbiuzzu" wrote:

thanks for your solution, i'll try it, but isn't it possible to write
something really compact as that ??

For i = 15 To 123 Step 12

Windows("MyTyM").worksheets("saved line-ups").Range("B" & i).value =
Windows("MyTyM2").worksheets("saved line-ups").Range("B" & i).value

next i

etc.. etc

or instead of "Windows" some other word that indicates source &
destination file ??

VB Macro for Excel
 

i move sets of 10 cells at a time as you can see in

Range("E" & i + 1 & ":E" & i + 11).Select

but the command line:

Windows("MyTyM").worksheets("saved line-ups").Range("B" & i).value =
Windows("MyTyM2").worksheets("saved line-ups").Range("B" & i).value

seems to be incorrect....

VB Macro for Excel
 

To move a single value you can use the .Value. To move a range use copy and
pastespecial. Avoid using the selects. They are innefficiant and probematic.

dim wksCopyTo as worksheet
dim wksCopyFrom as worksheet

set wkscopyto = thisworkbook.sheets("saved line-ups")
set wkscopyfrom = workbooks("My.T.y.M2.xls").sheets("saved line-ups")

For i = 15 To 123 Step 12
wkscopyto.Range("B" & i).Value = wkscopyfrom.Range("B" & i).Value
wkscopyfrom.Range("E" & i + 1 & ":E" & i + 11).copy
wkscopyto.Range("E" & i).PasteSpecial Paste:=xlPasteValues
'...

next i
set wkscopyfrom = nothing
set wkscopyto = nothing
--
HTH...

Jim Thomlinson


"furbiuzzu" wrote:

i move sets of 10 cells at a time as you can see in

Range("E" & i + 1 & ":E" & i + 11).Select

but the command line:

Windows("MyTyM").worksheets("saved line-ups").Range("B" & i).value =
Windows("MyTyM2").worksheets("saved line-ups").Range("B" & i).value

seems to be incorrect....

VB Macro for Excel
 

thanks to all for yout tips...

...but i've found on my own the solution i was lookin for.

just 3 lines of code and a magical word: "Workbooks"

this is the macro i wanted to implement

Sub copiaform_da_MytyM2_a_MytyM()
'
' copiaform_da_MytyM2_a_MytyM Macro
' Macro registrata il 19/10/2005 da fulvio
Dim i As Integer

For i = 15 To 123 Step 12

Workbooks("My.T.y.M.xls").Worksheets("saved line-ups").Range("B" &
i).Value = Workbooks("My.T.y.M2.xls").Worksheets_("saved
line-ups").Range("B" & i).Value

Workbooks("My.T.y.M.xls").Worksheets("saved line-ups").Range("C" & i +
1 & ":C" & i + 11).Value =
Workbooks_("My.T.y.M2.xls").Worksheets("saved line-ups").Range("C" & i
+ 1 & ":C" & i + 11).Value

Workbooks("My.T.y.M.xls").Worksheets("saved line-ups").Range("E" & i +
1 & ":E" & i + 11).Value =
Workbooks_("My.T.y.M2.xls").Worksheets("saved line-ups").Range("E" & i
+ 1 & ":E" & i + 11).Value

Next i

End Sub