VBA inserting copied cells using inputbox

[dannyh]

Hi All

Been trying to figure this out all afternoon had to give up !

I have a range of cells that have been selected and copied via my macro

I am trying to get an input box to allow the user to select the location where the copied cells should be inserted.

workbook has number of sheets PN1252, PN1356, PN1458, ect ect and a summary sheet.

the summary sheet has the sheet names listed in col A, each separated by 3 rows.

The ideal senario would be that each of the copied selections on the various sheets (PN) would be inserted into the summary sheet under the relevant headings automatically, failing that a user input box where the correct location is able to be set would be ok.

For i = 1 To ws_num
    ActiveWorkbook.Worksheets(i + 2).Activate
    
    'Subtotaling data, copying visible cells only and pasting to new location ready to be inserted
    
    Dim lastRow As Integer
lastRow = Range("d5000").End(xlUp).row
Rows(lastRow & ":" & lastRow).Delete shift:=xlUp

ActiveSheet.Outline.ShowLevels RowLevels:=2

numRows = Cells(Rows.Count, "f").End(xlUp).row - 2
numCols = 2

Range("d1", Cells(Rows.Count, "d").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

Dim newRange As Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows - 1, numCols))
'MsgBox newRange.Address
newRange.Select
Selection.SpecialCells(xlCellTypeVisible).Copy
    
ActiveCell.Offset(0, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Trying to use inputbox to set insert area !!! code has been edited and changed quite a bit so is probably quite wrong !

Dim N As Range
Dim sR As String, sS As String, sTemp As String

sR = Selection.Address
sS = ActiveSheet.Name
sTemp = "'" & sS & "'!" & sR

starting_ws.Activate 'goes to summary sheet

Set N = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
Application.GoTo N

Range(sR).Copy Destination:=Range(sTemp)

Range(sR).Cut Destination:=N
Range(sTemp).Range("A1").Resize(Selection.Rows.Count, Selection.Columns.Count).Copy Destination
'Range(sTemp).Insert shift:=xlDown


Next i

All help appreciated.

Dan

 

[dannyh]

If anyone has any ideas relating to the above, I would be most appreciative.

 

[Norie]

Dan

What problems are you having with the posted code?

 

[dannyh]

Dan

What problems are you having with the posted code?

Hi Norie

Run-time error 1004
Copy method of range class failed

on this line of code

Range(sTemp).Range("A1").Resize(Selection.Rows.Count, Selection.Columns.Count).Copy Destination

Like I say I probably haven't got the best solution for the problem but I just need it working now as have about 10 jobs that are waiting for me to look at !

Thanks Dan

 

[Norie]

Dan

Is this the range of cells you want to copy?

Range("d1", Cells(Rows.Count, "d").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

And this is where the user selects where to copy to?

Set N = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)

 

[dannyh]

Dan

Is this the range of cells you want to copy?

Range("d1", Cells(Rows.Count, "d").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

That selects 1 cell Norie, this code then chooses the range that cell is attached to and selects it

Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows - 1, numCols))
newRange.Select
[\code]


And this is where the user selects where to copy to?
[code]
Set N = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)

Yes the user picks the location via that code

[\QUOTE]

Thanks Dan

 

[Norie]

If you want to copy newRange to N all you should need is this.

newRange.Copy N

 

[dannyh]

If you want to copy newRange to N all you should need is this.

newRange.Copy N

Hi Norie

I need to insert my newRange at N not copy as it over writes other information otherwise.

Thank Dan