Copy Paste Transpose VBA Conundrum

Pokanoket

New Member
Joined
Dec 14, 2003
Messages
3
I have the following code that takes the data from the Input section, based a counter, and puts it into the Calculation section. Then, I need to copy paste transpose each Group to the Output section. The code I have now is:

'sub macro1
Dim calc_section As Range
Dim counter As Integer
For counter = 1 To 5
Range("calc_section").Select
Selection.Copy
Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
counter = counter + 1
Next
End Sub

And the Colo HTML looks like this....thanks in advance.
Temp.xls
ABCDEFGHI
1
2GroupCounter1
3
4InputGroupLoanTypeLTVStateDocTypeCountNoteAmt
51Grey80%TNNone5500,000
62Blue90%WAFull2250,000
73Green100%TNExcept1100,000
84Black85%MNFull1125,000
9
10CalcSectionGroup1
11LoanTypeGrey
12LTVTN
13State0.8
14DocTypeNone
15Count5
16NoteAmt500,000
17Average100,000
18
19Output
20GroupLoanTypeLTVStateDocTypeCountNoteAmtAverage
211GreyTN80%None5500000100000
Sheet1
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Your For loop is using a wrong syntex.


Use:
For counter = 1 to 5

Range("calc_section").Select
Selection.Copy
Range("C21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

next counter


what range is calc_section defining?
 
Upvote 0
Based on what i understand, your code should work with the correction of the "for" loop syntex. You should define the range within your counter such as:


rangeVariable = .range(.cells(i,1), .cells(i,8))


Make sure to correct your FOR loop to be in a syntex:

For i = 1 to 5
{
}
next i


Regards,
 
Upvote 0
The following code , when inserted into the sheet you showed above, will change the values in the specified ranges everytime you change the value in B2.... is that what you want ???

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo NoFind
Dim Rw As Integer
If Target.Address = "$B$2" Then
Rw = Range("B5:B9").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole).Row
If Len(Rw) > 1 Then
Range("B" & Rw & ":H" & Rw).Copy
Range("E10:E16").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("B21:H21").Value = Range("B" & Rw & ":H" & Rw).Value
Else
NoFind:
Range("E10:E17").ClearContents
Range("B21:H21").ClearContents
End If
End If
Application.CutCopyMode = False
Range("B2").Select
End Sub

TO INSTALL:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE
 
Upvote 0
Firstly, I’m not sure why you need the Calc Section – the only calculation being done is getting the average (E17). Can you add this to I5:I8? If you can, you don’t need the Calc Section. If you can’t, you still don’t need the calc Section. In cell I21, put

=IF($B$21= “’’,“”, $H$21/$G$21)

Secondly, it seems that all you are doing is selecting a group number and then copying the associated data to another location. If so, try the following procedure that uses an Advanced Filter.
Copy paste transpose.xls
ABCDEFGHIJ
1
2GroupCounter4FALSE
3
4InputGroupLoanTypeLTVStateDocTypeCountNoteAmtAverage
51Grey80%TNNone5500,000100,000
62Blue90%WAFull2250,000125,000
73Green100%TNExcept1100,000100,000
84Black85%MNFull1125,000125,000
9
10
11
12
13
14
15
16
17
18
19Output
20GroupLoanTypeLTVStateDocTypeCountNoteAmtAverage
214Black85%MNFull1125,000125,000
22
Sheet1


In cell J2, put:
=$B$2=B5

In a sheet module, put:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
     Range("B4:I8").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J1:J2"), CopyToRange:=Range("B20:I20"), Unique:=False
End Sub
In the above macro, your ranges are hard-coded e.g. range B4:B18. See Nimrod's macro for ideas in obtaining more flexibility with your range selection.

HTH

Mike
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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