ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How can i shorten this macro? (https://www.excelbanter.com/excel-programming/357859-how-can-i-shorten-macro.html)

LMI
How can i shorten this macro?
 
Sub LFSG_HB()
Sheets("Data ESG_HB").Select
Range("F3:F22").Select
Selection.Copy
Sheets("Raw Data").Select
Range("BO3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("G3:G22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO5").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("H3:H22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO7").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("I3:I22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO9").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("J3:J22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO11").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("K3:K22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO13").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("L3:L22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO15").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("M3:M22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO17").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("N3:N22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO19").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("COMMANDS").Select
End Sub

Neo[_4_]
How can i shorten this macro?
 
write a function to do the lines repeated. Created a array with values
and traverse throuhg loop and call function in loop by passing array
values.

Bob Phillips[_6_]
How can i shorten this macro?
 
Sub LFSG_HB()

Sheets("Raw Data").Select
With Sheets("Data ESG_HB")
.Range("F3:F22").Copy Range("BO3")
.Range("G3:G22").Copy Range("BO5")
.Range("H3:H22").Copy Range("BO7")
.Range("I3:I22").Copy Range("BO9")
.Range("J3:J22").Copy Range("BO11")
.Range("K3:K22").Copy Range("BO13")
.Range("L3:L22").Copy Range("BO15")
.Range("M3:M22").Copy Range("BO17")
.Range("N3:N22").Copy Range("BO19")
End With
Application.CutCopyMode = False
Sheets("COMMANDS").Select
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LMI" wrote in message
...
Sub LFSG_HB()
Sheets("Data ESG_HB").Select
Range("F3:F22").Select
Selection.Copy
Sheets("Raw Data").Select
Range("BO3").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("G3:G22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO5").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("H3:H22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO7").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Sheets("Data ESG_HB").Select
Range("I3:I22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO9").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("J3:J22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO11").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("K3:K22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO13").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("L3:L22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO15").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("M3:M22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO17").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("Data ESG_HB").Select
Range("N3:N22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Raw Data").Select
Range("BO19").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Sheets("COMMANDS").Select
End Sub

LMI
How can i shorten this macro?
 
Ok now i figured it wasn;t gonna be that complicated. Im relatively a beggner
tat this can u explain more?

"Neo" wrote:

write a function to do the lines repeated. Created a array with values
and traverse throuhg loop and call function in loop by passing array
values.


All times are GMT +1. The time now is 12:25 AM.

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