logo
down
shadow

VBA Copy & Transpose Data Range


VBA Copy & Transpose Data Range

By : user2950232
Date : November 17 2020, 11:52 AM
this will help PasteSpecial is unavailable with the .Cut method, but not the .Copy method. When I changed
code :
Worksheets("Sheet1").Range("E6:E14").Cut
Worksheets("Sheet1").Range("E6:E14").Copy
Sub Test()

Worksheets("Sheet1").Range("E6:E14").Copy

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True

Worksheets("Sheet1").Range("E6:E14").Clear 

End Sub


Share : facebook icon twitter icon
Excel VBA - Range.Copy transpose paste

Excel VBA - Range.Copy transpose paste


By : Albert Poghosyan
Date : March 29 2020, 07:55 AM
it fixes the issue I'm trying to something very simple, but I seem to be stuck. I am following the help menu for PasteSpecial but I cannot seem to get my code to work without an error.
code :
Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
Copy specific range to first available row in another worksheet and transpose

Copy specific range to first available row in another worksheet and transpose


By : pspaul
Date : March 29 2020, 07:55 AM
I hope this helps you . The code below runs smoothly, and copies the data from Workbook1 to Workbook2 on the first available row, starting from column B. I need to know when the data was submitted, and therefore want the time and date to be inserted into the first available cell in column A each time data is submtited. for your help! , Try this, it's a one line solution
code :
Option Explicit

Sub MoveData()

'Define variables
Dim Workbook1 As Workbook
Dim Workbook2 As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long, DestLastRow As Long

'Set wb
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

'Copy (In this case I want to copy range D4:D7 only, and this will be the same every time)
ThisWorkbook.Sheets("Sheet1").Range("D4:D7").Copy

'Open Workbook 2 and paste data (transposed) on first available row starting in column B
Set Workbook2 = Workbooks.Open("H:\Macro FSC\Forsøk10\Workbook2.xlsm")
With Workbook2.Sheets("Sheet1")
    ' find last row with data in destination workbook "wbDatabase.xlsm"
    DestLastRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Row
     'paste special only values, and transpose
    .Range("B" & DestLastRow).PasteSpecial xlValues, Transpose:=True
'Added line here:
    .range("A1").Value = now
End With

'Save and close
Workbook2.Save
Workbook2.Close

End Sub
Copy Paste VBA Range from one to another worksheet Loop and transpose the data

Copy Paste VBA Range from one to another worksheet Loop and transpose the data


By : Nakata
Date : March 29 2020, 07:55 AM
Hope that helps I'm a little confused with what exactly you want to do but would this do?:
First code: Not using transpose
code :
Sub Test()

Dim X As Long, Y As Long, Z As Long

Z = 2
For X = 2 To 2420 Step 10
    For Y = 2 To 10
        Sheets("RME").Cells(Z, Y).Value = Sheets("CME").Cells(X + (Y - 1), 10).Value
    Next Y
    Z = Z + 1
Next X

End Sub
Sub Test()

Dim X As Long, Z As Long
Dim RNG1 As Range, RNG2 As Range

Sheets("CME").Activate
Z = 2
For X = 2 To 2420 Step 10
    Set RNG1 = Sheets("CME").Range(Cells(X, 10), Cells(X + 9, 10))
    Set RNG2 = Sheets("RME").Cells(Z, 2)
    RNG1.Copy
    RNG2.PasteSpecial Transpose:=True
    Z = Z + 1
Next X
Sheets("RME").Activate

End Sub
Copy and Past Transpose of a Range

Copy and Past Transpose of a Range


By : cs11
Date : March 29 2020, 07:55 AM
Hope this helps Using With Simplified, everything starting with a dot (".") is referring to the object in the With statement. In your version without the With statement, what ever starts with a dot (".") should have been preceded by book.Sheets(2) Not sure if 'Tellurian Inc' is with or without the SPACE. Correct if necessary. The Code
code :
Sub CommandButton2_Click()

    Dim Report As Workbook
    Dim lRow As Long

    Set Report = Workbooks.Open("S:\HR Compensation\Christapher Martin\" _
            & "Tellurian Inc Job Pricing\Job Families and Competencies " _
            & "- Report.xlsm")

    With ThisWorkbook.Sheets(2)
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        .Range(.Cells(8, 3), .Cells(lRow, 3)).Copy
    End With

    Report.Sheets(1).Range("B2").PasteSpecial Transpose:=True

    Application.CutCopyMode = False

End Sub
Sub CommandButton2_Click()

    Dim Report As Workbook
    Dim lRow As Long

    Set Report = Workbooks.Open("S:\HR Compensation\Christapher Martin\" _
            & "Tellurian Inc Job Pricing\Job Families and Competencies " _
            & "- Report.xlsm")

    With ThisWorkbook.Sheets(2)
        lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        .Range(.Cells(8, 3), .Cells(lRow, 3)).Copy
    End With

    With Report.Sheets(1)
        .Range("B2").PasteSpecial Transpose:=True
        '.Parent.Close True ' Save changes and close workbook.
    End With

    Application.CutCopyMode = False

End Sub
Copy range and transpose paste into row that contains cell

Copy range and transpose paste into row that contains cell


By : user3574801
Date : March 29 2020, 07:55 AM
hope this fix your issue I have noticed on your code that you have not referenced the sheet of Range("O3:O44"). So when you run the code, it will Select and Copy the Range("O3:O44")of the active sheet.
To avoid this confusion, avoid using .Select and .Activate as much as possible especially when dealing with multiple sheets. When referencing Ranges, always include the sheet you are targeting to.
code :
Range("O3:O44").Select
Selection.Copy
Worksheets("Sheet1").Range("O3:O44").Copy 
'range to copy with sheet reference
Worksheets("Sheet1").Range("O3:O44").Copy
'range where previous range will be pasted, also with sheet reference
Worksheets("Sheet2").Range("A" & nStart & ":AP" & nEnd).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Related Posts Related Posts :
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org