VBA Copy Range by User Prompt

In the attached workbook I would like to create a memo based on info that is the on sheet the "Create a Cost Memo" button is clicked on.  I was able to create a new sheet but I am having trouble figuring out how to populate the Project Name  - Project number (B6 & B7 on 1530252 to C3 on SIA and  Project Manager name B5 (15300252) to C4 (SIA).  Then I would like to be able to prompt and select a range of cells on 15300252 like c30:H34 and copy over to SIA starting at A30. Is this possible?
EE-Sample-PTT-2014.07.08.xlsm
jmac001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
I am having trouble figuring out how to populate the Project Name  - Project number (B6 & B7 on 1530252 to C3 on SIA
This is how:
Sheets("SIA").Range(C3").Value = Sheets("1530252 ").Range("B6").Value & " " & Sheets("1530252 ").Range("B7").Value

Open in new window


and  Project Manager name B5 (15300252) to C4 (SIA)
Sheets("SIA").Range(C4").Value = Sheets("1530252 ").Range("B5").Value

Open in new window


Then I would like to be able to prompt and select a range of cells on 15300252 like c30:H34 and copy over to SIA starting at A30.
    Dim rngCopyFrom As Range
    
    Sheets("1530252 ").Activate
    On Error Resume Next
    Set rngCopyFrom = Application.InputBox("Enter the range from which you want to copy", Type:=8)
    On Error GoTo 0

    If Not rngCopyFrom Is Nothing Then
        rngCopyFrom.Copy ThisWorkbook.Sheets("SIA").Range("A30")
    End If

Open in new window

0
jmac001Author Commented:
Question: for the sheet name can you make dynamic since the user will be able to create the name of the sheet?
0
MacroShadowCommented:
assuming you add the code to the CMemo sub-routine, use the following:
Sheets(shtname)......
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

jmac001Author Commented:
I am a little confused where would I place Sheets(shtname) in the 1st example that you sent back if both values I gave you are dynamic?

Sheets("SIA").Range(C3").Value = Sheets("1530252 ").Range("B6").Value & " " & Sheets("1530252 ").Range("B7").Value

Open in new window


The Project Number (1530252) is going to be dynamic and the new memo sheet (SIA) is going to be dynamic (recommending naming convention of name project number - M - numberic value (1530252-M-1))
0
MacroShadowCommented:
Previously I showed the concept, here is the practical application:
Dim strSheetNameSource As String
Dim strSheetNameDestination As String

strSheetNameSource = "whatever"
strSheetNameDestination = "whatever else"

Sheets(strSheetNameDestination).Range(C3").Value = Sheets(strSheetNameSource).Range("B6").Value & " " & Sheets(strSheetNameSource).Range("B7").Value

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac001Author Commented:
I will give it a try and let you know if I am successful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.