Copy different columns cells ask range from user in put box

[ssahmed77]

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
Do
SheetName = InputBox("Please Enter the Sheet name from where Data is required for copying?")

On Error GoTo ErrorHandler2


Sheets(SheetName).Select
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
myrange.Copy

Sheets("ConsolidatedStatus").Select
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)//

ExitPoint:

Exit Sub


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

End Sub

 

[acw]

Hi

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

Sheets("ConsolidatedStatus").Select
range(mypasterange.address).Select

to select the equivalent range on the output sheet.

HTH

Tony

 

[ssahmed77]

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

Thanks
Regards
SS

 

[acw]

SS

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.

Tony