View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
SHO SHO is offline
external usenet poster
 
Posts: 5
Default 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.