Crosswords2 mins ago
Editing Excel Macros
4 Answers
I recorded a macro that takes the first 7 days of records from a vertical calendar (one row per day), then pastes that into a horizontal calendar (one row per WEEK).
I copied and pasted it and changed the cell references to make it do Wekk no.s 1 and 2.
Is there any way to edit the macro to make it copy, transpose and paste for 52 weeks?
Sub Transpose1()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B3").Select
End Sub
Sub Transpose2()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C11:C18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B4").Select
End Sub
I copied and pasted it and changed the cell references to make it do Wekk no.s 1 and 2.
Is there any way to edit the macro to make it copy, transpose and paste for 52 weeks?
Sub Transpose1()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B3").Select
End Sub
Sub Transpose2()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C11:C18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B4").Select
End Sub
Answers
Best Answer
No best answer has yet been selected by Barquentine. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.I'm not going to do the whole lot for you and there might well be a better way... but the first three weeks are
Sub Transpose1()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Sheets("Sheet1").Select
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("C11:C18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("C19:C25").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
End Sub
The part below is just repeating with the ranges changed everytime
Sheets("Sheet1").Select
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sub Transpose1()
'
' Transpose1 Macro
' Macro recorded 21/12/2012 by user
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Sheets("Sheet1").Select
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("C11:C18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("C19:C25").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
End Sub
The part below is just repeating with the ranges changed everytime
Sheets("Sheet1").Select
Range("C4:C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sorry if I've misunderstood but would this not help?
http:// office. microso ft.com/ en-gb/e xcel-he lp/swit ch-tran spose-c olumns- and-row s-HP010 224502. aspx
http://
Thanks Chuckfickens - that's how I thought I'd have to do it - but wanted to see if there's a built in automation that understands that you want to change the ranges and does it for you! Like that drag function when Excel autocompletes dates or days of the week without my needing to type them. I'll bet there's a way somewhere. Thanks for your help anyway and Happy Christmas Everybody for tomorrow!