I thought this would be easy

[want2b]

I found Von Pookie's code from 12/16/05 and it does exactly what I need. But I would like to be able to be prompted for the range to copy. I can go to the code and change ("A1") to the range I need ("A92:L94") for example The column needed will always be A & L but the rows will change each time.

How can I have the macro ask me for the first row and I answer 92 or some other number
Then ask me for the last row and I answer 94 or some other number?

Von Pookie Code:
Sub test()
Dim ws As Worksheet, PasteRng As Range

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
Set PasteRng = Sheets("Summary").Range
("65536").End(xlUp).Offset(1,0)
With ws
.Range("A1").Copy Destination:=PasteRng
End With
End If
Next ws
End Sub

I'm sure the information I need is here somewhere. I'm just not smart enough to know what to search for.

My next step would be to only copy the row to "summary" when there is a value in column G of the above named range.

I haven't even begun to try and figure that out - and probably wouldn't be able to anyway.

Thanks

I hope this did not go twice. When I submited it the first time it went back to where I log on, and did not seem to post.

 

[a7n9]

Try this:

Sub test() 
Dim ws As Worksheet, PasteRng As Range 

For Each ws In ThisWorkbook.Worksheets 
If ws.Name <> "Summary" Then 
Set PasteRng = Application.InputBox(prompt := "Enter Range to Paste", type := 8)
With ws 
.Range("A1").Copy Destination:=PasteRng 
End With 
End If 
Next ws 
End Sub

This will ask you for a range.

 

[want2b]

Thank you for the reply
I tried but was only for it to give me the input box saying "Enter Range to Paste"
When I enter A92:L94 and press enter another input box pops up saying "Enter Range to Paste" as far as I can see nothing else happens.

The area to paste is working fine in the original code. What I need to change is what gets coppied. In the original code this is A1,

That is what I'd like changed. It would be different each time I would run the macro.
I used A92:L94 as an example which works when I replace the A1 in the original code.
The next time I run the macro I might use A51:L51 or A216:L225

Thanks again

 

[a7n9]

I didn't understand your problem correctly, sorry.

OK try this

Sub test() 
Dim ws As Worksheet, PasteRng As Range 
Dim whichrow as long
For Each ws In ThisWorkbook.Worksheets 
If ws.Name <> "Summary" Then 
Set PasteRng = Sheets("Summary").Range 
("65536").End(xlUp).Offset(1,0) 
With ws 
.Range("A" & whichrow).Copy Destination:=PasteRng 
End With 
End If 
Next ws 
End Sub

 

[want2b]

Thanks again for the reply
This time I get :
Run time error '1004'
Application-defined or object-defined errors

When I press debug
Set PasteRng = Sheets("Summary").Range("65536").End(xlUp).Offset(1, 0)
is highlighted in yellow.

I was not prompted for a range.
since A seems to be included I guess I need to figure how to get (my starting row) " L (my ending row) into whichrow?

Thanks

 

[want2b]

Okay Thanks
I messed around with your code for a while and got something that I think will work.

Sub Test()
Dim ws As Worksheet, PasteRng As Range
Dim startrow As Long
Dim endrow As Long
startrow = InputBox(Prompt:="Enter startrow")
endrow = InputBox(Prompt:="Enter endrow")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
With ws
.Range("A" & startrow & ":L" & endrow).Copy Destination:=PasteRng
End With
End If
Next ws
End Sub

Thank you much Von Pookie and A7N9

This runs much faster than I had expected (54 sheets) so for the last part I plan to go to the Summary sheet and go down the G column and just delete the rows with no values.