I thought this would be easy

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696
Try this:

Code:
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

Board Regular
Joined
Jan 27, 2004
Messages
89
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

Well-known Member
Joined
Sep 15, 2004
Messages
696
I didn't understand your problem correctly, sorry.

OK try this
Code:
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

Board Regular
Joined
Jan 27, 2004
Messages
89
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

Board Regular
Joined
Jan 27, 2004
Messages
89
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,004
Members
412,304
Latest member
citrus
Top