A better Macro for Copying Data

[drinese18]

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("D314").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

 

[cht13er]

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("D314").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

 

[gimme_this_gimme_that]

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.