Make Macro smaller and condensed

D

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
 
Sort by date Sort by votes
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
 
Upvote 0
jimrward said:
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
Click to expand...


Appricate your help but it did not work. It gave me a Run-time error'1004' Select method of range class failed...
any ideas?
 
Upvote 0
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
 
Upvote 0
jimrward said:
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
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
323
MikeVol
MikeVol
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
506
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
304
kbk
K
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
497
MARK858
MARK858
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
125
mumps
mumps

Forum statistics

Threads
1,226,840
Messages
6,193,283
Members
453,788
Latest member
drcharle

Share this page

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