How can I make this code more efficient?
 

I need to copy and paste certain rows/columns from one workbook to
another, and I have like hundreds of these. How can I cut it down so
the macro runs faster and I don't have to repeat this code a hundred
times?

Here's my sample:

' Copy and Paste [A]
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("A" & int_Download_Firstrow & ":A" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("A" & int_Template_Firstrow & ":A" &
int_Template_Lastrow).Select
Selection.PasteSpecial

' Copy and Paste [b]
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("B" & int_Download_Firstrow & ":B" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("B" & int_Template_Firstrow & ":B" &
int_Template_Lastrow).Select
Selection.PasteSpecial

' Copy and Paste Region [i]
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("I" & int_Download_Firstrow & ":I" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("C" & int_Template_Firstrow & ":C" &
int_Template_Lastrow).Select
Selection.PasteSpecial


Many thanks!!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=567074

How can I make this code more efficient?
 

You probably want to use Workbook and Worksheet objects something more like
this...

Dim wbkCopy as workbook
Dim wbkPaste as workbook
Dim shtCopy as worksheet
Dim shtPaste as worksheet

set wbkcopy = workbooks("" & var_Download_Filename)
set wbkpaste = workbooks("" & var_Template_filename)
set shtcopy = wbkcopy.sheets("" & var_Download_sheetname)
set shtpaste = wbkpaste.sheets("" & var_Template_sheetname)

shtCopy.Range("A" & int_Download_Firstrow & _
":A" & int_Download_Lastrow).Copy

shtPaste.Range("A" & int_Template_Firstrow & _
":A" & int_Template_Lastrow).PasteSpecial xlValues
Application.cutcopymode = false

--
HTH...

Jim Thomlinson


"Sethaholic" wrote:
[i]

I need to copy and paste certain rows/columns from one workbook to
another, and I have like hundreds of these. How can I cut it down so
the macro runs faster and I don't have to repeat this code a hundred
times?

Here's my sample:

' Copy and Paste [A]
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("A" & int_Download_Firstrow & ":A" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("A" & int_Template_Firstrow & ":A" &
int_Template_Lastrow).Select
Selection.PasteSpecial

' Copy and Paste [b]
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("B" & int_Download_Firstrow & ":B" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("B" & int_Template_Firstrow & ":B" &
int_Template_Lastrow).Select
Selection.PasteSpecial

' Copy and Paste Region
Windows("" & var_Download_Filename).Activate
Sheets("" & var_Download_sheetname).Select
Range("I" & int_Download_Firstrow & ":I" &
int_Download_Lastrow).Select
Selection.Copy

Windows("" & var_Template_filename).Activate
Sheets("" & var_Template_sheetname).Select
Range("C" & int_Template_Firstrow & ":C" &
int_Template_Lastrow).Select
Selection.PasteSpecial


Many thanks!!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=567074