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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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

P
  • Solved
Counter in For Each loop
Replies
2
Views
171
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
156
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
297
Azamamir
A
J
  • Question
I could use Help with a loop that I am doing Manually. I struggle with the loops.
Replies
2
Views
356
DanteAmor
DanteAmor
D
  • Question
VBA optimization advice
Replies
3
Views
81
bobsan42
bobsan42

Forum statistics

Threads
1,214,422
Messages
6,119,395
Members
448,891
Latest member
tpierce

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