Copy different columns cells ask range from user in put box


New Member
Nov 21, 2005
Hi to all I would like a macro If any one can help me I am very thankfull.i want to perform following action
I have 7 different sheets in a work book, I want to copy some columns ranges for e:g like from sheet1 i want to copy A3:G31 from sheet 2 A3:h31 and so on..
I want to paste the copied value on a sheet which I alreday make for this purpose.
I want when macro run ask user for Sheet name, Range for Copy, Range for Paste.

And I want in the destination sheet in column A number of rows counted and no of rows time sheet name is inserted in column A.

Kindly Help me out.

So far I tried some thing But i have problems in this code

Sub UpdateStatus()

' Macro recorded 24.11.2005 by D045523
' Varaibles declaration
Dim SheetName As String
Dim myrange As Excel.Range
Dim mypasterange As Excel.Range
Dim J As Integer

For J 1 To 7
SheetName = InputBox("Please Enter the Sheet name from where Data is required for copying?")

On Error GoTo ErrorHandler2

Set myrange = Application.InputBox _
("Please select the Source Sheet range for copying in the format Like A3:G30", , , , , , , 8)
On Error GoTo ErrorHandler2
Set mypasterange = Application.InputBox _
("Please select the destination sheet range input in format like A3:G31", , , , , , , 8)
On Error GoTo ErrorHandler2

On Error GoTo ErrorHandler2

mypasterange.Select //( I have Problem here)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveCell.FormulaR1C1 = SheetName
Selection.AutoFill Destination:= , Type:=xlFillDefault
//(here I want that from mypasterange number of rows count and in column A of destination sheet Sheetname is inserted no of rowes times)//


Exit Sub

MsgBox Err.Number & vbCrLf & Err.Description, vbCritical
Resume ExitPoint

End Sub

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Because you have changed sheets after you have selected the output range, you are getting the problem. You could do something like


to select the equivalent range on the output sheet.


Upvote 0
Another Problem not solved yet

Thanks fpr your reply I refine it with your solution and it look like working fine except a problem that in the first cell of paste it written wrong info not the copied value.

Now the second problem is still there how I count number of rows from mypaste value as I need to insert the sheet name in Column A of the destination sheet row times, like if the pasting value consist of 33 rows, then in column A 33 times sheet name is inserted.

Kindly give me some solution

Upvote 0

Lets back up a bit. You are copying data from 7 sheets, and putting that data into a colsolidated sheet, along with the name of the source sheet. Is there anything consistent on each of the data sheets that will allow this range to be selected without the user input? Is it going to be a consistent set of columns on all the sheets (but with varying number of rows) and a different source row?

Can you also provide some details on the structure of the source sheets.

Upvote 0

Forum statistics

Latest member

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
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 "".
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