MACRO help

  • Thread starter Thread starter Neil Holden
  • Start date Start date
N

Neil Holden

Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then


Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With
 
I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,true
 
Hi

Like you suggested, use PasteSpecial and transpose values:

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial Paste:=xlPasteValues,
Transpose:=True
Application.CutCopyMode = False
End If
End Sub

Regards,
Per
 
They must be a simple way to do this, thanks for your attempt though much
appreciated. I am struggling with this and unfortunately your solution
didn't work.

Back to the drawing board.

Thanks again AB

AB said:
I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,true



Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With
Click to expand...

.
Click to expand...
 
Try

wsDest.Range("A" & lngRow).Resize(1, rngTemp.Rows.Count) = _
WorksheetFunction.Transpose(rngTemp.Value)

If this post helps click Yes
---------------
Jacob Skaria


Neil Holden said:
They must be a simple way to do this, thanks for your attempt though much
appreciated. I am struggling with this and unfortunately your solution
didn't work.

Back to the drawing board.

Thanks again AB

AB said:
I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,true



Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With
Click to expand...

.
Click to expand...
Click to expand...
 
This is super Jacob but the only trouble i'm having now is i need it to paste
from row 2 because i have title headers in row 1.

Thanks alot!!

Jacob Skaria said:
Try

wsDest.Range("A" & lngRow).Resize(1, rngTemp.Rows.Count) = _
WorksheetFunction.Transpose(rngTemp.Value)

If this post helps click Yes
---------------
Jacob Skaria


Neil Holden said:
They must be a simple way to do this, thanks for your attempt though much
appreciated. I am struggling with this and unfortunately your solution
didn't work.

Back to the drawing board.

Thanks again AB

AB said:
I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,true



On Nov 25, 9:56 am, Neil Holden <[email protected]>
wrote:
Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With

.
Click to expand...
Click to expand...
Click to expand...
 
If you need the data in Column B (cell data combined with comma delimiter)
using formula then you will have to use multiple IF statements; but again if
you have a big list even that would not be possible. You will have ti use a
VBA solution. Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the below formula.

Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, 1)), _
strLookupValue, vbTextCompare) = 0 Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1)
End Function

If this post helps click Yes
 
Neil, the below line which you already have should return 2 if you have
headers in row 1

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

If this post helps click Yes
---------------
Jacob Skaria


Neil Holden said:
This is super Jacob but the only trouble i'm having now is i need it to paste
from row 2 because i have title headers in row 1.

Thanks alot!!

Jacob Skaria said:
Try

wsDest.Range("A" & lngRow).Resize(1, rngTemp.Rows.Count) = _
WorksheetFunction.Transpose(rngTemp.Value)

If this post helps click Yes
---------------
Jacob Skaria


Neil Holden said:
They must be a simple way to do this, thanks for your attempt though much
appreciated. I am struggling with this and unfortunately your solution
didn't work.

Back to the drawing board.

Thanks again AB

:

I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,true



On Nov 25, 9:56 am, Neil Holden <[email protected]>
wrote:
Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With

.
Click to expand...
Click to expand...
Click to expand...
 
Its row 2 not column B? is this possible?

Jacob Skaria said:
If you need the data in Column B (cell data combined with comma delimiter)
using formula then you will have to use multiple IF statements; but again if
you have a big list even that would not be possible. You will have ti use a
VBA solution. Try this UDF (User Defined function). From workbook launch VBE
using Alt+F11. From menu Insert a Module and paste the below function.Close
and get back to workbook and try the below formula.

Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, 1)), _
strLookupValue, vbTextCompare) = 0 Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1)
End Function

If this post helps click Yes
Click to expand...
 
Back
Top