• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PASTING BETWEEN SHEETS TO NEXT EMPTY ROW

Hello all,

I've been going circles with the VBA code below trying to figure out how to copy vertical data from one sheet and paste just the values transposed horizontally to another in the first empty cell in column be of another sheet. This really is my first attempt to do any VBA programming and I haven't made much progress. Or use the macro recorder ultimately to get me as far as I am below. I think it would work except I cannot determine how to get it to paste to the next empty row. Any help would be great. Let me know what kind of clarifying information I should've included. Thanks again.


Code:
Sub COPYPASTER()
'
' COPYPASTER Macro
' COPING DATA FROM SHEET PLOT PASTING TRANSPOSED VALUES ONLY TO TO NEXT EMPTY LINE IN COLUMN B OF SHEET SQR

    Sheets("Plot").Range("C25:C29").Select
  
    Application.CutCopyMode = False
  
    Selection.Copy
  
    Sheets("SQR").Select
  
    Selection.End(xlUp).Select
  
    ActiveCell.Offset(1, 0).Range("A1").Select
  
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
  
      
End Sub
 

Kenneth Hobson

Active Member
Code:
Sub COPYPASTER()
  Sheets("Plot").Range("C25:C29").Copy
  Sheets("SQR").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
    PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=True
  Application.CutCopyMode = False
End Sub
 
Kenneth, Thanks so much, that takes a real load off. Worked perfectly! Would you mind explaining how this line of your code works so I can learn to "catch fish" too?
Code:
Sheets("SQR").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
 

Kenneth Hobson

Active Member
Sure, that is my preference. rows.count gets the last row number in the sheet. Cells() is normally used with the column number rather than the column letter which I like better. Range() can be used which allows intellisense to know what properties and methods that it can dropdown after a period. Cells() does not. So, at the last cells in column, if we press End and then Up keys, the last cell with a data value is selected. Offset() let's us move it however many rows or columns that we want, which is one row in this case.
 

joycechen232

New Member
The code provided works for me, but it only pastes as columns in the second sheet when I would like to paste as rows? For example I would like to copy paste from sheet 1 A1:F3 into A1:F3 in sheet 2. However, in sheet 2 it pastes as A1:C6, so the data is vertical instead of horizontal. Is there any way to alter the code slightly so this will work?
 
Top