On Apr 8, 10:36*am, drinese18
wrote:
I am trying to create a better macro to copy data from one worksheet to the
next, the thing is, the worksheet I am copying from is not in the same format
as the worksheet I am copying to, therefore the values from worksheet 1 has
to go in different specific cells in worksheet 2, I recorded a macro below
and it works but I know it probably won't work for the other worksheets I
have since the name of the worksheets get changed sometimes, the recorded
macro can be seen below:

Sub Macro1()

* * Range("D3:D14").Select
* * Selection.Copy
* * ChDir "C:\Documents and Settings\jermaine_wanyou\Desktop\FACTBOOK SYSTEM"
* * Workbooks.Open Filename:= _
* * * * "C:\Documents and Settings\jermaine_wanyou\Desktop\FACTBOOK
SYSTEM\Argentina Bucket.xls"
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B7").Select
* * Application.WindowState = xlMinimized
* * Windows("Cal.xls").Activate
* * Range("E3:E14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B8").Select
* * Windows("Cal.xls").Activate
* * Range("F3:F14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B11").Select
* * Windows("Cal.xls").Activate
* * Range("G3:G14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B12").Select
* * Windows("Cal.xls").Activate
* * Range("H3:H14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B16").Select
* * Windows("Cal.xls").Activate
* * Range("I3:I14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B13").Select
* * Windows("Cal.xls").Activate
* * Range("L3:L14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("B17").Select
* * Windows("Cal.xls").Activate
* * ActiveWindow.ScrollColumn = 2
* * ActiveWindow.ScrollColumn = 3
* * Range("M4:M14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Windows("Argentina Bucket.xls").Activate
* * Range("C17").Select
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=True
* * Range("A1").Select
* * Application.CutCopyMode = False
* * ActiveCell.FormulaR1C1 = "Argentina 2007"
* * Range("A1").Select
* * ActiveWorkbook.Save
* * ActiveWindow.Close
* * Range("H21").Select
End Sub

So I'm just wondering is there anyone who can help me with this


You can use
Dim WB1 as Workbook
Dim WB2 as Workbook

Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open Filename:= "C:\Documents and Settings
\jermaine_wanyou\Desktop\FACTBOOK SYSTEM\Argentina Bucket.xls"


I thiiiiiiink .. someone else can confirm?

HTH

Chris