Macros

[Khalil Handal]

Hi,
I have the following macro in workbook named 7.xls.
I need to copy data form other workbooks (12) with different names. Both
workbooks will be open.
I do not want to edit the macro each time and change the name of the
(class1.xls) to (class1.xls) and so forth for the rest of the 11 workbooks,
and then run the macro.
Is there a way to adjust the macro so that it takes the opened first
workbook no matter what name it has?

I will keep workbook 7.xls open, run the macro, use "save as" for 7.xls
then close class1 file and open class2 file, run the macro again, save as
and so forth ....
I hope that the explanation is clear!

Sub Macro1()
'
Windows("class1.xls").Activate
Range("B14:E64").Select
Selection.Copy
Windows("7.xls").Activate
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("class1.xls").Activate

End Sub

 

[Dave Peterson]

I would write the code to work against the active sheet. So you'd have to do
that part manually:

Option Explicit
Sub Macro1()

dim RngToCopy as range
dim DestCell as range

set rngtocopy = activesheet.range("b14:e64")
set destcell = thisworkbook.worksheets("Sheet9999").range("B14")

rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

(untested, uncompiled. Watch for typos.)

ThisWorkbook refers to the workbook with the code (7.xls, right???). Change the
sheetname to the correct name.

 

[Khalil Handal]

worked fine. Thank you
Khali Handal

I would write the code to work against the active sheet. So you'd have to
do
that part manually:

Option Explicit
Sub Macro1()

dim RngToCopy as range
dim DestCell as range

set rngtocopy = activesheet.range("b14:e64")
set destcell = thisworkbook.worksheets("Sheet9999").range("B14")

rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

(untested, uncompiled. Watch for typos.)

ThisWorkbook refers to the workbook with the code (7.xls, right???).
Change the
sheetname to the correct name.