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

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

This is a bit off topic. But apt.

It is always more reliable to read a text file and iterate - and store
each line into a Worksheet - than it is to open a Worksheet and copy
and paste data from one Workbook to another.

Reading data makes it so you don't have to copy large amounts of data
into memory which can *really* slow down performance. And I mean so
slow that Excel hangs.

It's also easier to filter out blank lines or bad data.

Also, it's easier to copy data from multiple Workbooks into a single
sheet - because you have a row number that you can increment. The
thing is, with VBA you can execute shift-downarrow to get the last row
- but you might be shift-downarrowing a column that has blanks before
the last line. So you might lose data and not know it.

With the line-at-a-time approach you can create several VBA macros -
each to handle different formats - depending upon the data you're
provided. Sometimes there might be 4 columns - other times 5.