Donate SIGN UP

Editing Excel Macros

Avatar Image
Barquentine | 10:14 Mon 24th Dec 2012 | Computers
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
Gravatar

Answers

1 to 4 of 4rss feed

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

I've never been much good at using macros, but I would have thought there would be an easier way to achieve this as I feel presenting rows as columns and columns as rows would not be an uncommon requirement. I don't know how to do it though.
Question Author
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!

1 to 4 of 4rss feed

Do you know the answer?

Editing Excel Macros

Answer Question >>

Related Questions