Make Macro smaller and condensed

dracron

New Member
Joined
Jan 10, 2014
Messages
32
How would I condese this into a small amount of lines and add additional rows/columns if I needed to

Sub Converter()
'
' Converter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Sheets("Unconverted").Select
Range("B2:AP2").Select
Selection.Copy
Sheets("Converted").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B3:AP3").Select
Selection.Copy
Sheets("Converted").Select
Range("C43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Sheets("Unconverted").Select
Range("B4:AP4").Select
Selection.Copy
Sheets("Converted").Select
Range("C84").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B5:AP5").Select
Selection.Copy
Sheets("Converted").Select
Range("C125").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B6:AP6").Select
Selection.Copy
Sheets("Converted").Select
Range("C166").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B7:AP7").Select
Selection.Copy
Sheets("Converted").Select
Range("C207").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B8:AP8").Select
Selection.Copy
Sheets("Converted").Select
Range("C248").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B9:AP9").Select
Selection.Copy
Sheets("Converted").Select
Range("C289").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B10:AP10").Select
Selection.Copy
Sheets("Converted").Select
Range("C330").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B11:AP11").Select
Selection.Copy
Sheets("Converted").Select
Range("C371").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B12:AP12").Select
Selection.Copy
Sheets("Converted").Select
Range("C412").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B13:AP13").Select
Selection.Copy
Sheets("Converted").Select
Range("C453").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B14:AP14").Select
Selection.Copy
Sheets("Converted").Select
Range("C494").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B15:AP15").Select
Selection.Copy
Sheets("Converted").Select
Range("C535").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B16:AP16").Select
Selection.Copy
Sheets("Converted").Select
Range("C576").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B17:AP17").Select
Selection.Copy
Sheets("Converted").Select
Range("C617").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B18:AP18").Select
Selection.Copy
Sheets("Converted").Select
Range("C658").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B19:AP19").Select
Selection.Copy
Sheets("Converted").Select
Range("C699").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B20:AP20").Select
Selection.Copy
Sheets("Converted").Select
Range("C740").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B21:AP21").Select
Selection.Copy
Sheets("Converted").Select
Range("C781").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B22:AP22").Select
Selection.Copy
Sheets("Converted").Select
Range("C822").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B23:AP23").Select
Selection.Copy
Sheets("Converted").Select
Range("C863").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B24:AP24").Select
Selection.Copy
Sheets("Converted").Select
Range("C904").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B25:AP25").Select
Selection.Copy
Sheets("Converted").Select
Range("C945").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B26:AP26").Select
Selection.Copy
Sheets("Converted").Select
Range("C986").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B27:AP27").Select
Selection.Copy
Sheets("Converted").Select
Range("C1027").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B28:AP28").Select
Selection.Copy
Sheets("Converted").Select
Range("C1068").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B29:AP29").Select
Selection.Copy
Sheets("Converted").Select
Range("C1109").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B30:AP30").Select
Selection.Copy
Sheets("Converted").Select
Range("C1150").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Unconverted").Select
Range("B31:AP31").Select
Selection.Copy
Sheets("Converted").Select
Range("C1191").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
try the following condensed versio whilst not the purest solution it uses loops

Code:
Sub Converter()
'
' Converter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'

cop = 2
For j = 2 To 31
    Sheets("Unconverted").Range("B" & j & ":AP" & j).Select
    Selection.Copy
    Sheets("Converted").Select
    Range("C" & cop).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    cop = cop + 41
Next j
end sub
 

dracron

New Member
Joined
Jan 10, 2014
Messages
32
try the following condensed versio whilst not the purest solution it uses loops

Code:
Sub Converter()
'
' Converter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'

cop = 2
For j = 2 To 31
    Sheets("Unconverted").Range("B" & j & ":AP" & j).Select
    Selection.Copy
    Sheets("Converted").Select
    Range("C" & cop).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    cop = cop + 41
Next j
end sub


Appricate your help but it did not work. It gave me a Run-time error'1004' Select method of range class failed...
any ideas?
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
i should have tested first, try the following

Code:
Sub Converter()
'
' Converter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
cop = 2
For j = 2 To 31
    Sheets("Unconverted").Range("B" & j & ":AP" & j).Copy
    Sheets("Converted").Range("C" & cop).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    cop = cop + 41
Next j
End Sub
 

dracron

New Member
Joined
Jan 10, 2014
Messages
32
i should have tested first, try the following

Code:
Sub Converter()
'
' Converter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
cop = 2
For j = 2 To 31
    Sheets("Unconverted").Range("B" & j & ":AP" & j).Copy
    Sheets("Converted").Range("C" & cop).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    cop = cop + 41
Next j
End Sub

This works wonderfuly. I very much appreciate you taking the time to look at this. May I ask where you learnt this skill. I am relatively new in this field and am trying to learn as much as possible.
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
i am a mainframe programmer of many years, VBA is still new to me, i just looked at your code and worked out the pattern and sequence and took it from there
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top