Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Copy Range by User Prompt

Posted on 2014-07-16
6
Medium Priority
?
233 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:jmac001
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 40199818
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
 

Author Comment

by:jmac001
ID: 40200139
Question: for the sheet name can you make dynamic since the user will be able to create the name of the sheet?
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 40201168
assuming you add the code to the CMemo sub-routine, use the following:
Sheets(shtname)......
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jmac001
ID: 40202892
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
 
LVL 28

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 40203248
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
 

Author Comment

by:jmac001
ID: 40205151
I will give it a try and let you know if I am successful.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question