Copy the last N range of columns and paste into the next blank column in N row

pamelarub

New Member
Joined
Oct 19, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi guys, I am not really familiarized with VBA, any help is really appreciated.!!
I am trying to copy a range of columns ("JMM:JSX") that have been hidden and paste them into the next blank cell of the 2nd row from the same sheet.

I have this but it keeps throwing me errors.

Sub test()
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Pack")
Set pasteSheet = Worksheets("Pack")

copySheet.Range("JMM:JSX").Copy
With pasteSheet
.Cells(2, .Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone
End With
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to MrExcel Message Board.
You cann't copy entire column and then paste it in part of column.
Try this:
VBA Code:
Sub test()
Dim Lr As Long, Ws As Worksheet
Set Ws = Worksheets("Pack")
Lr = Ws.Range("JMM" & Rows.Count).End(xlUp).Row
Ws.Range("JMM2:JSX" & Lr).Copy
Ws.Cells(2, Ws.Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
End Sub
 
Upvote 0
Hey Maabadi, thanks for your quick and efficient reply! :)
It works awesome, just that the range copied is being pasted without the formatting and formulas.
Is there a way to keep the formulas once pasted in the next cell?

Thank you!!
 
Upvote 0
How about
VBA Code:
Sub test()
Dim Lr As Long, Ws As Worksheet
Set Ws = Worksheets("Pack")
Lr = Ws.Range("JMM" & Rows.Count).End(xlUp).Row
Ws.Range("JMM2:JSX" & Lr).Copy Ws.Cells(2, Ws.Columns.Count).End(xlToLeft).Offset(0, 1)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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