ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   lenghty code (https://www.excelbanter.com/excel-programming/422687-lenghty-code.html)

Murray
lenghty code
 
HI i have some code that seems very long to preform a simple task, is thera
way to get the same result with a shorter code

Workbooks.Open ("C:\MIS Reports output - crystal\Xcel
export\NSW_This_Month_TOPSalesmen.xls")
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F7").Select
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C6").Select
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Help is appreicated

Rick Rothstein
lenghty code
 
Instead of making us try and figure out what your code is doing by reading
it, why don't you make our job easier by telling us what it does?

--
Rick (MVP - Excel)


"Murray" wrote in message
...
HI i have some code that seems very long to preform a simple task, is
thera
way to get the same result with a shorter code

Workbooks.Open ("C:\MIS Reports output - crystal\Xcel
export\NSW_This_Month_TOPSalesmen.xls")
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F7").Select
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C6").Select
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Help is appreicated


Barb Reinhardt
lenghty code
 
It would help if you'd provide the worksheet name that you are copying and
pasting from/to.

Barb Reinhardt

"Murray" wrote:

HI i have some code that seems very long to preform a simple task, is thera
way to get the same result with a shorter code

Workbooks.Open ("C:\MIS Reports output - crystal\Xcel
export\NSW_This_Month_TOPSalesmen.xls")
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F7").Select
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C6").Select
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Help is appreicated

Murray
lenghty code
 
I create reports using crystal and export to excel and up till now it has
been copy and paste values into a formatted spreadsheet. It is time comsuming
as i have about 12 of every month, so ithought write some code and get excel
to do the work every month, it just that it seems quite long to do the task.

Export from crystal
Unmerge the cells
copy and paste special, values a cell at a time

"Rick Rothstein" wrote:

Instead of making us try and figure out what your code is doing by reading
it, why don't you make our job easier by telling us what it does?

--
Rick (MVP - Excel)


"Murray" wrote in message
...
HI i have some code that seems very long to preform a simple task, is
thera
way to get the same result with a shorter code

Workbooks.Open ("C:\MIS Reports output - crystal\Xcel
export\NSW_This_Month_TOPSalesmen.xls")
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F7").Select
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C6").Select
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Help is appreicated



Murray
lenghty code
 
THe worksheets i copy from vary
NSW_This_Month_TOPSalesmen.xls
NSW_YTD_TOPSalesmen.xls
NSW_This_month_TOPProducts.xls
NSW_YTD_TOPProducts

"Barb Reinhardt" wrote:

It would help if you'd provide the worksheet name that you are copying and
pasting from/to.

Barb Reinhardt

"Murray" wrote:

HI i have some code that seems very long to preform a simple task, is thera
way to get the same result with a shorter code

Workbooks.Open ("C:\MIS Reports output - crystal\Xcel
export\NSW_This_Month_TOPSalesmen.xls")
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F7").Select
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C6").Select
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("NSW_This_Month_TOPSalesmen.xls").Activate
Range("K11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0810 NSW Store Reports.xls").Activate
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Help is appreicated


All times are GMT +1. The time now is 03:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com