Macro to copy data
 

I have a file with data in the same cells that I wish to copy into another
file. I have to do this constantly and wondered if I could achieve this with
a macro? The data I wish to copy is in various cells in the first file e.g.
A1, A2, A5, C1, C2, C5 etc, therefore I have named this as a range called
copy_data.

I then need to bring this into a seperate file into various cells e.g. cell
A1 from the first file needs to go into B1 of the second file. I cannot set a
formula up for this as the first file will be a different filename each time
(although the layout remains the same).

Does naming the range copy_data in the first file help or will I have to
relate each cell seperately to achieve this macro?

Any ideas as to the code required?

Thanks in advance.

Macro to copy data
 

Hi, Sho. I'm not real good at moving an entire non-contiguous range like
that as a block. I think if I were setting this up for me, and the same
cells always went to the same places (A1 first file always went to B1 second
file, etc.), then I would probably wind up with something like what's below.
I'm sure there are better and prettier ways to do it, but that's how I would
do it for me. (Note: maybe I'm assuming too much, but it sounds like you're
using some kind of "template" file, that you open, paste the data into, then
SaveAs a new name?? I've written this code presuming that; if that's not
the case, it can be easily modified.)

Ed

Sub Copy_Data()

' Assumes file to be copied from
' is open and active; this is wbk1.
' wbk2 will be the receiving file.

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strName As String

' Declare wbk1
Set wbk1 = ActiveWorkbook
' Open receiving file as wbk2
strName = Application.GetOpenFilename(",*.xls")
If strName < "False" Then
Set wbk2 = Workbooks.Open(strName)
End If

' Copy cells.
wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value
wbk2.Sheets("Sheet1").Range("E4").Value =
wbk1.Sheets("Sheet1").Range("D1").Value

' Close wbk1 without changing
wbk1.Close SaveChanges:=False
' Save wbk2
Application.Dialogs(xlDialogSaveAs).Show

End Sub


"Sho" wrote in message
...
I have a file with data in the same cells that I wish to copy into another
file. I have to do this constantly and wondered if I could achieve this
with
a macro? The data I wish to copy is in various cells in the first file
e.g.
A1, A2, A5, C1, C2, C5 etc, therefore I have named this as a range called
copy_data.

I then need to bring this into a seperate file into various cells e.g.
cell
A1 from the first file needs to go into B1 of the second file. I cannot
set a
formula up for this as the first file will be a different filename each
time
(although the layout remains the same).

Does naming the range copy_data in the first file help or will I have to
relate each cell seperately to achieve this macro?

Any ideas as to the code required?

Thanks in advance.

Macro to copy data
 

Ed,

Thanks for your help - I entered the following code but for some reason it
comes up with a Compiled Expression error on the copy cells part and displays
the text in red. Am I doing something wrong?

Sub Copy_data()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strname As String

Set wbk1 = ActiveWorkbook
strname = "C:\master test"
If strname < "False" Then
Set wbk2 = Workbooks.Open(strname)
End If

wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value

wbk1.Close Savechanges:=False

End Sub




"Ed" wrote:

Hi, Sho. I'm not real good at moving an entire non-contiguous range like
that as a block. I think if I were setting this up for me, and the same
cells always went to the same places (A1 first file always went to B1 second
file, etc.), then I would probably wind up with something like what's below.
I'm sure there are better and prettier ways to do it, but that's how I would
do it for me. (Note: maybe I'm assuming too much, but it sounds like you're
using some kind of "template" file, that you open, paste the data into, then
SaveAs a new name?? I've written this code presuming that; if that's not
the case, it can be easily modified.)

Ed

Sub Copy_Data()

' Assumes file to be copied from
' is open and active; this is wbk1.
' wbk2 will be the receiving file.

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strName As String

' Declare wbk1
Set wbk1 = ActiveWorkbook
' Open receiving file as wbk2
strName = Application.GetOpenFilename(",*.xls")
If strName < "False" Then
Set wbk2 = Workbooks.Open(strName)
End If

' Copy cells.
wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value
wbk2.Sheets("Sheet1").Range("E4").Value =
wbk1.Sheets("Sheet1").Range("D1").Value

' Close wbk1 without changing
wbk1.Close SaveChanges:=False
' Save wbk2
Application.Dialogs(xlDialogSaveAs).Show

End Sub


"Sho" wrote in message
...
I have a file with data in the same cells that I wish to copy into another
file. I have to do this constantly and wondered if I could achieve this
with
a macro? The data I wish to copy is in various cells in the first file
e.g.
A1, A2, A5, C1, C2, C5 etc, therefore I have named this as a range called
copy_data.

I then need to bring this into a seperate file into various cells e.g.
cell
A1 from the first file needs to go into B1 of the second file. I cannot
set a
formula up for this as the first file will be a different filename each
time
(although the layout remains the same).

Does naming the range copy_data in the first file help or will I have to
relate each cell seperately to achieve this macro?

Any ideas as to the code required?

Thanks in advance.

Macro to copy data
 

Having just posted that I have now managed to figure it out! - Thanks for
your help.

Sho

"Sho" wrote:

Ed,

Thanks for your help - I entered the following code but for some reason it
comes up with a Compiled Expression error on the copy cells part and displays
the text in red. Am I doing something wrong?

Sub Copy_data()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strname As String

Set wbk1 = ActiveWorkbook
strname = "C:\master test"
If strname < "False" Then
Set wbk2 = Workbooks.Open(strname)
End If

wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value

wbk1.Close Savechanges:=False

End Sub




"Ed" wrote:

Hi, Sho. I'm not real good at moving an entire non-contiguous range like
that as a block. I think if I were setting this up for me, and the same
cells always went to the same places (A1 first file always went to B1 second
file, etc.), then I would probably wind up with something like what's below.
I'm sure there are better and prettier ways to do it, but that's how I would
do it for me. (Note: maybe I'm assuming too much, but it sounds like you're
using some kind of "template" file, that you open, paste the data into, then
SaveAs a new name?? I've written this code presuming that; if that's not
the case, it can be easily modified.)

Ed

Sub Copy_Data()

' Assumes file to be copied from
' is open and active; this is wbk1.
' wbk2 will be the receiving file.

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strName As String

' Declare wbk1
Set wbk1 = ActiveWorkbook
' Open receiving file as wbk2
strName = Application.GetOpenFilename(",*.xls")
If strName < "False" Then
Set wbk2 = Workbooks.Open(strName)
End If

' Copy cells.
wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value
wbk2.Sheets("Sheet1").Range("E4").Value =
wbk1.Sheets("Sheet1").Range("D1").Value

' Close wbk1 without changing
wbk1.Close SaveChanges:=False
' Save wbk2
Application.Dialogs(xlDialogSaveAs).Show

End Sub


"Sho" wrote in message
...
I have a file with data in the same cells that I wish to copy into another
file. I have to do this constantly and wondered if I could achieve this
with
a macro? The data I wish to copy is in various cells in the first file
e.g.
A1, A2, A5, C1, C2, C5 etc, therefore I have named this as a range called
copy_data.

I then need to bring this into a seperate file into various cells e.g.
cell
A1 from the first file needs to go into B1 of the second file. I cannot
set a
formula up for this as the first file will be a different filename each
time
(although the layout remains the same).

Does naming the range copy_data in the first file help or will I have to
relate each cell seperately to achieve this macro?

Any ideas as to the code required?

Thanks in advance.

Macro to copy data
 

Sorry about that! I forgot the width limitations of the NG might break the
lines in odd and useless places! Glad it was useful to you.

Ed

"Sho" wrote in message
...
Having just posted that I have now managed to figure it out! - Thanks for
your help.

Sho

"Sho" wrote:

Ed,

Thanks for your help - I entered the following code but for some reason
it
comes up with a Compiled Expression error on the copy cells part and
displays
the text in red. Am I doing something wrong?

Sub Copy_data()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strname As String

Set wbk1 = ActiveWorkbook
strname = "C:\master test"
If strname < "False" Then
Set wbk2 = Workbooks.Open(strname)
End If

wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value

wbk1.Close Savechanges:=False

End Sub




"Ed" wrote:

Hi, Sho. I'm not real good at moving an entire non-contiguous range
like
that as a block. I think if I were setting this up for me, and the
same
cells always went to the same places (A1 first file always went to B1
second
file, etc.), then I would probably wind up with something like what's
below.
I'm sure there are better and prettier ways to do it, but that's how I
would
do it for me. (Note: maybe I'm assuming too much, but it sounds like
you're
using some kind of "template" file, that you open, paste the data
into, then
SaveAs a new name?? I've written this code presuming that; if that's
not
the case, it can be easily modified.)

Ed

Sub Copy_Data()

' Assumes file to be copied from
' is open and active; this is wbk1.
' wbk2 will be the receiving file.

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim strName As String

' Declare wbk1
Set wbk1 = ActiveWorkbook
' Open receiving file as wbk2
strName = Application.GetOpenFilename(",*.xls")
If strName < "False" Then
Set wbk2 = Workbooks.Open(strName)
End If

' Copy cells.
wbk2.Sheets("Sheet1").Range("B1").Value =
wbk1.Sheets("Sheet1").Range("A1").Value
wbk2.Sheets("Sheet1").Range("C2").Value =
wbk1.Sheets("Sheet1").Range("B1").Value
wbk2.Sheets("Sheet1").Range("D3").Value =
wbk1.Sheets("Sheet1").Range("C1").Value
wbk2.Sheets("Sheet1").Range("E4").Value =
wbk1.Sheets("Sheet1").Range("D1").Value

' Close wbk1 without changing
wbk1.Close SaveChanges:=False
' Save wbk2
Application.Dialogs(xlDialogSaveAs).Show

End Sub


"Sho" wrote in message
...
I have a file with data in the same cells that I wish to copy into
another
file. I have to do this constantly and wondered if I could achieve
this
with
a macro? The data I wish to copy is in various cells in the first
file
e.g.
A1, A2, A5, C1, C2, C5 etc, therefore I have named this as a range
called
copy_data.

I then need to bring this into a seperate file into various cells
e.g.
cell
A1 from the first file needs to go into B1 of the second file. I
cannot
set a
formula up for this as the first file will be a different filename
each
time
(although the layout remains the same).

Does naming the range copy_data in the first file help or will I
have to
relate each cell seperately to achieve this macro?

Any ideas as to the code required?

Thanks in advance.