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.
|