ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   User selects existing sheet to paste to (https://www.excelbanter.com/excel-programming/438223-user-selects-existing-sheet-paste.html)

canary2211
User selects existing sheet to paste to
 
Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
Selection.copy
Application.InputBox( _
"use mouse to select worksheet", Type:=8)
ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. All fail! Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance

Per Jessen
User selects existing sheet to paste to
 
Hi
Look at theese two macros. The second code did not work due to a typo error!

Sub aaa()
Dim DestRng As Variant

Set TargetSheet = ActiveSheet
On Error Resume Next
Set DestRng = Application.InputBox( _
"use mouse to select any cell on destination worksheet", Type:=8)
If DestRng Is Nothing Then Exit Sub
On Error GoTo 0
TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")
End Sub


Sub bbb()
SelectAnswer = InputBox("Tell me a sheet name.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
If Err.Number 0 Then
msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")
Exit Sub
End If
On Error Goto 0
Range("D4").Value = "Done it!"

End Sub

Regards,
Per

"canary2211" skrev i meddelelsen
...
Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
Selection.copy
Application.InputBox( _
"use mouse to select worksheet", Type:=8)
ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. All fail! Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance


canary2211
User selects existing sheet to paste to
 
Thank you Per - very helpful.

I must be doing something else wrong as I got syntax error in both of
these :


in aaa at Set DestRng = Application
and in bbb at msg = MsgBox("The sheet: " & SelectAnswer & " does not
exists

I tried to run them on both the destination and target sheets.

Thanks

Nick






On Jan 10, 5:52*pm, "Per Jessen" wrote:
Hi
Look at theese two macros. The second code did not work due to a typo error!

Sub aaa()
Dim DestRng As Variant

Set TargetSheet = ActiveSheet
On Error Resume Next
Set DestRng = Application.InputBox( _
* * "use mouse to select any cell on destination worksheet", Type:=8)
If DestRng Is Nothing Then Exit Sub
On Error GoTo 0
TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")
End Sub

Sub bbb()
SelectAnswer = InputBox("Tell me a sheet name.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
If Err.Number 0 Then
* * msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")
* * Exit Sub
End If
On Error Goto 0
Range("D4").Value = "Done it!"

End Sub

Regards,
Per

"canary2211" skrev i ...



Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. *I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
* *Selection.copy
* *Application.InputBox( _
"use mouse to select worksheet", Type:=8)
* *ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. *All fail! *Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance- Hide quoted text -

- Show quoted text -

Per Jessen
User selects existing sheet to paste to
 
Hi Nick

I think the error's are due to word wrap in you news reader.

In aaa, the statements below has to be one line in the macro editor:

Set DestRng = Application.InputBox("use mouse to select any cell on
destination worksheet", Type:=8)

and

TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")

In bbb this has to be on one line:

msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")

Hopes this helps.
....
Per

"canary2211" skrev i meddelelsen
...
Thank you Per - very helpful.

I must be doing something else wrong as I got syntax error in both of
these :


in aaa at Set DestRng = Application
and in bbb at msg = MsgBox("The sheet: " & SelectAnswer & " does not
exists

I tried to run them on both the destination and target sheets.

Thanks

Nick






On Jan 10, 5:52 pm, "Per Jessen" wrote:
Hi
Look at theese two macros. The second code did not work due to a typo
error!

Sub aaa()
Dim DestRng As Variant

Set TargetSheet = ActiveSheet
On Error Resume Next
Set DestRng = Application.InputBox( _
"use mouse to select any cell on destination worksheet", Type:=8)
If DestRng Is Nothing Then Exit Sub
On Error GoTo 0
TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")
End Sub

Sub bbb()
SelectAnswer = InputBox("Tell me a sheet name.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
If Err.Number 0 Then
msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")
Exit Sub
End If
On Error Goto 0
Range("D4").Value = "Done it!"

End Sub

Regards,
Per

"canary2211" skrev i
...



Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
Selection.copy
Application.InputBox( _
"use mouse to select worksheet", Type:=8)
ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. All fail! Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance- Hide quoted text -

- Show quoted text -

canary2211
User selects existing sheet to paste to
 
Brilliant! Works beautifully! Thank you!





On Jan 11, 7:59*am, "Per Jessen" wrote:
Hi Nick

I think the error's are due to word wrap in you news reader.

In aaa, the statements below has to be one line in the macro editor:

Set DestRng = Application.InputBox("use mouse to select any cell on
destination worksheet", Type:=8)

and

TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")

In bbb this has to be on one line:

msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")

Hopes this helps.
...
Per





"canary2211" skrev i meddelelsen
news:08d8f3c1-f554-4932-b423-0f0cd1ba6__BEGIN_MASK_n#9g02mG7!__...__END_MASK_i ...
Thank you Per - very helpful.

I must be doing something else wrong as I got syntax error in both of
these :

in aaa at Set DestRng = Application
and in bbb at msg = MsgBox("The sheet: " & SelectAnswer & " does not
exists

I tried to run them on both the destination and target sheets.

Thanks

Nick

On Jan 10, 5:52 pm, "Per Jessen" wrote:



Hi
Look at theese two macros. The second code did not work due to a typo
error!

Sub aaa()
Dim DestRng As Variant

Set TargetSheet = ActiveSheet
On Error Resume Next
Set DestRng = Application.InputBox( _
"use mouse to select any cell on destination worksheet", Type:=8)
If DestRng Is Nothing Then Exit Sub
On Error GoTo 0
TargetSheet.Range("A1:L6").Copy
Destination:=Sheets(DestRng.Parent.Name).Range("A1 ")
End Sub

Sub bbb()
SelectAnswer = InputBox("Tell me a sheet name.")
On Error Resume Next
Worksheets(SelectAnswer).Activate
If Err.Number 0 Then
msg = MsgBox("The sheet: " & SelectAnswer & " does not exists",
vbExclamation + vbOKOnly, "Error")
Exit Sub
End If
On Error Goto 0
Range("D4").Value = "Done it!"

End Sub

Regards,
Per

"canary2211" skrev i
...

Very grateful for any help: I am a novice at this.

I have a spreadsheet with multiple sheets, already named (lets say
Mon, Tues, Wed).

A macro visits other Excel files, retrieves data by copying. I want
to allow the user to select the paste destination (sheet name only,
cells A1 to end). The cells are all in the same format etc, all I need
is to let the user specify - either by typing, or by selecting from a
list, which sheet (Mon,Tue,Wed) to paste to, and then let the macro
continue to paste and do other things.

I have tried :

Range("A1:L6").Select
Selection.copy
Application.InputBox( _
"use mouse to select worksheet", Type:=8)
ActiveSheet.Paste
End Sub

and I have tried

SelectAnswer = InputBox("Tell me a sheet name.")
Worksheets(Answer).Activate
Range("D4").Value = "Done it!"

and I have tried to create a drop down list. All fail! Some error
checking, or a drop down selection, would be helpful to limit user
error but not essential.

PS Working in Excel2007

Thanks in advance- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


All times are GMT +1. The time now is 12:04 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com