Hi Eric
Try :
Sub Mtest()
Dim LR As Long
LC = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LC
'change sheet name here
'Use below line if you dont want to copy header of columns
'Sheet1.Range(Cells(2, i), Cells(LR, i)).Copy
Sheet1.Range(Cells(1, i), Cells(LR, i)).Copy
Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Paste:=xlValues, Transpose:=True
Next
End Sub
HTH
Mahesh
On Thu, Mar 8, 2012 at 8:54 PM, Erick C <boricua2...@gmail.com> wrote:
> Hi everybody -
>
> I am hoping someone may be able to help me out. I am still quite a
> novice with writing VBA, so I am sure there is an easy way to make
> what I am trying to do. I am working in Excel 2010.
> I have a spreadsheet with a tab that has data vertically from column A
> through column ZW. I am trying to copy a specific range and paste
> special/trasnpose the column info into a row in a different tab. I am
> trying to figure out an easier way to write some VBA to go to the
> first range, copy, go to the first line in the other tab, paste, go
> back and move right 7 columns to the next range, copy, back to the
> other tab, move down one line and paste, and so on...
> I have a bit of code that kind of illustrates what I am doing. Is
> there a better way to do this so I do not have to write a giant string
> that goes all the way through column ZA, basically repeating over 100
> times, or should I just keep on going with what I have now?
>
> Sheets("CMFX First 30").Select
> Range("E8:E407").Select
> Selection.Copy
> Sheets("Combined Sheet").Select
> Range("B2").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
> Sheets("CMFX First 30").Select
> Range("L8:L407").Select
> Application.CutCopyMode = False
> Selection.Copy
> Sheets("Combined Sheet").Select
> Range("B3").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
> Sheets("CMFX First 30").Select
> Range("S8:S407").Select
> Selection.Copy
> Sheets("Combined Sheet").Select
> Range("B4").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
>
> Any help I can get would be greatly appreciated!
>
> Thanks!
>
> Erick
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
