Copy, Transpose paste to next blank row.

Shoop

New Member
Joined
Nov 13, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Many good threads but still struggling with what is hopefully a simple problem to solve.

I have been able to create a macro that copies and pastes the values cell by cell but this is the limit of my macro/VBA abilities and does not get me to the goal. What I hope can done is, after copying a range from " wokbook1", the macro will transpose paste the values to the next available row, is this possible?

1605728469008.png

Thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I think this is what your looking for. Better if I could see your macro.
Change the range value to the first cell with data in that row, looks like yours is "Template" in your example and looks like it is in cell B2
It will start at that cell and look down for the next empty cell and paste there.




Sheets("Summary").Select

Range("B2").Select


ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Upvote 0
Thank you for your guidance Glenn. I should have included the macro, as far as I could take it. Here it is, can you help me integrate your solution?

Sub Hour_Summary()
'
' Hour_Summary Macro
'

'
Workbooks.Open Filename:="Z:\Ben\bid docs_Ben\Summary 1.xlsx"
Windows("Commercial Bid Sheet - DRAFT.xlsx").Activate
Range("D16:D19").Select
Selection.Copy
Windows("Summary 1.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("Commercial Bid Sheet - DRAFT.xlsx").Activate
End Sub
 
Upvote 0
Try this. Looks like your missing the sheet name you want to paste your range in to,( Example "Sheet1") and the cell you want it to start at looking for an empty cell. I put "B2" as the first cell you want to check if there is any information in it.

Workbooks.Open Filename:="Z:\Ben\bid docs_Ben\Summary 1.xlsx"
Windows("Commercial Bid Sheet - DRAFT.xlsx").Activate
Range("D16:D19").Select
Selection.Copy
Windows("Summary 1.xlsx").Activate

Sheets("your sheet name here").Select
Range("B2").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("Commercial Bid Sheet - DRAFT.xlsx").Activate
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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