How can I make this code more efficient?

[Sethaholic]

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!!

[Jim Thomlinson]

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 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
>
>