• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy range from another sheet and paste into "active sheet"

Lazarous

New Member
the intent is to have a sheet with pre-populated data (In this case the sheet called "Data" that I would like to use as a template to insert within certain rows on any given active working sheet.

The attached has two macros.

1- Insert Section: Copies and inserts range from the same sheet
2- Insert Pre-Build: Meant to copy a range from the "Data" sheet and paste back on the active sheet, which in this case is sheet 1. Just remember sheet 1 could be renamed to anything else, so I can't reference the active sheet name in the macro.

I have tried to modify the "Insert Section" code to Insert a range from sheet "Data" into the active sheet, but I can't get it to work.

I want to be able from the active working sheet, to initiate the macro to copy a range on the "Data" sheet and paste it within the active working sheet at the nominated location.

I have attached a sample file, which will hopefully assist. When asked what row to insert the "Insert Section" or the "Insert Pre-build" Macro, select row 35.

Appreciate any assistance.
 

Attachments

  • Test Template - Copy (2).xlsm
    46.1 KB · Views: 7
This is a wild cross posting …​
So according to any Excel forum rules, you must add a link for each other forum thread and do the same on each other forum : Read this !
 
Thanks for the link …​
Which is the active worksheet where you want to paste some data ?​
 
Marc,
I want to paste from the sheet named "Data" into the sheet named "1".

Please remember the whilst Data will be constant in name, sheet 1 can be renamed.
 
No matters just working with worksheet codename rather than worksheet name …​
upload_2018-8-14_14-35-54-png.54497
… as the codename does not change when a worksheet is renamed.​
As it seems the sheet '1' is the active sheet so to copy some range from 'Data' worksheet​
just use the Range.Copy method as described in the VBA help : Sheet11.Range({guessSource}).Copy Range({guessDestination}) …​
Just replace each 'guess' part.​
 
Marc,
sheet 1 in this instance is the active sheet, but it there could be others, hence I was looking for the macro to work on any given active sheet.
 
No matters just using the worksheet reference ActiveSheet if necessary​
like my code sample in my previous post works with the active sheet …​
 
try:
Code:
Sub Insert_Prebuild2()
If ActiveSheet.Name <> "Data" Then
  StartPoint2 = Application.InputBox("Enter the Row number where you would like to add a new section, (Important Note: New sections can only be added at the last row of an existing section . i.e. Enter the last row number of the preceeding section )", "Section Location", Type:=1)
  If StartPoint2 <> False Then
    Set destn = Range("A" & StartPoint2)
    Set CellCheck = Range("A" & StartPoint2 + 1)

    If StartPoint2 > 21 And CellCheck.Value = 2 Then
      'Insertsect
      Application.ScreenUpdating = False
      ActiveSheet.Unprotect "Password"
      Set rngSource = Worksheets("Data").Range("$36:$49")
      rngSource.Copy
      'Application.DisplayAlerts = False 'enable this line and the other one 2 lines down to prevent existing name error showing.
      destn.Offset(1).Insert
      'Application.DisplayAlerts = True 'allows warnings to show again.
      destn.Offset(1).Resize(rngSource.Rows.Count).EntireRow.Hidden = False
      ActiveSheet.Protect Password:="Password", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, UserInterfaceOnly:=True
    Else    'LocationError
      MsgBox "ERROR : The Row number selected (" & StartPoint2 & ") was not the last row in an existing section. Please select a valid row."
    End If
  End If
Else
  MsgBox "You're on the Data sheet! Aborted"
End If
Application.ScreenUpdating = True
End Sub
 
Last edited:
Mate, that is exactly what I was after. Thank you so much.

If you don't mind I, I get the following message, to which I select "Yes to All". Is it possible to incorporate this into the code so that it doesn't pop up every time?

Thanks again. You really nailed what I was after.


76683
 
OK look like I found the fix for the prompt box:

Add
Application.DisplayAlerts = False
at the top of the macro and
Application.DisplayAlerts = True
at the bottom.


Just to be a further pain, instead of me modifying the code for each instance of a named range to be inserted, could I use a Userform to list the named ranges from a list, say in a sheet named "Named Ranges" at A1:A10, select the desired named range to be copied and inserted from the list?
 

Attachments

  • Test Template - Copy (2).xlsm
    56.4 KB · Views: 5
Last edited:
OK look like I found the fix for the prompt box:

Add
Application.DisplayAlerts = False
at the top of the macro and
Application.DisplayAlerts = True
at the bottom.
If you'd looked at the code you'd have seen commented-out lines above and below
destn.Offset(1).Insert
but I don't know if this is the same as selecting 'Yes to all'.

I'll look at the userform later.
 
Last edited:
In the attached a rudimentary userform added, brought up with the keyboard shortcut Ctrl+i.
Only named range's names beginning with Test_ (case insensitive) are added to the list.
Two ways to insert a range:
1. double-click a name in the listbox. The userform dismisses itself.
2. selecting a name and clicking the Insert button. The userform stays open.
 

Attachments

  • Chandoo47043Test Template - Copy (2).xlsm
    60.5 KB · Views: 3
Thank you it works great, but how do I get it to work if the range names doesn't start with "Test_"?

For example, Prebuild_
 
Easy answer, change the range names so they do start with Test_ !
To be a bit more friendly, the answer depends on a few things:
  • How likely are you going to want to change/add/remove the range names?
  • How friendly do you want those range names to appear as to the user? (We can separate what appears in the list box from the actual named range)?
  • Does it matter what order they appear in the list?
 
p45cal,

If we could set it up as per the following then:

1- Named ranges to add to the list will all start with "Prebuild_"
2- Because eventually there would be quite a few "Prebuild_ " named ranges, is it possible to firstly show a list, that once selected would then list only the "Prebuilds_" associated with the particular list?

For example:

a- "Prebuild_Lighting _", which would then list all named ranged starting with "Prebuild_Lighting_"
b- "Prebuild_Power_", which would then list all named ranged starting with "Prebuild_Power_"
c- "Prebuild_Conduit_", which would then list all named ranged starting with "Prebuild_Conduit_"
d- "Prebuild_Switchboard_", which would then list all named ranged starting with "Prebuild_Switchboard_"

e- There will be others, but I hope that once the code is established for the above, I can add to the lists.

3- The lists would be displayed alphabetically.

Thank you.
 
You're asking me to write your project for you!
I'll do something, but it won't be top of my priorities.
 
Thanks Pascal,

didn't mean for it to be arduous.

I though once I had the code I thought I could mod it suit, but when I changed the code to say "Prebuild_", instead on "Test_" as per below, and changed the range names to all start with "Prebuild_"nothing came up on the list.

Then I thought about not having a huge list, which is when I I thought it may work better with a master list, then subsequent lists.

I appreciate anything you can do help. I just wish I was as proficient with coding excel.

Thanks again , and in your own time as you say.


Code:
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
If Len(ListBox1.BoundValue) > 0 Then Insert_Prebuild2 ListBox1.BoundValue
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Insert_Prebuild2 ListBox1.BoundValue
Unload Me
End Sub

Private Sub UserForm_Initialize()
For Each nm In ThisWorkbook.Names
        If UCase(Left(nm.Name, 5)) = "Prebuild_" Then
            Me.ListBox1.AddItem nm.Name
        End If
    Next nm
End Sub
 
Change from
If UCase(Left(nm.Name, 5)) = "Prebuild_" Then
to
If UCase(Left(nm.Name, 9)) = "PREBUILD_" Then
 
Last edited:
Save me some slog;
1. Prepare a workbook with as many actual named ranges you intend to use as you can.
2. Then prepare, in a new worksheet in the same workbook, two tables, something like:
76754
where the first table contains the actual names of ranges in the workbook that you're going to be wanting to copy. It doesn't matter what they are because the user will only see the Friendly names you give in the next column. Next assign each actual name a number/letter/one-word description in the Group/List column (again, the user won't see this so it can be anything but best if you assign a single word so that us coders understand the context).
In the second table, repeat the Group/List of the first table, but only distinct/unique values, and give each a group a friendly name that only the users will see.
In both tables, have them in the order you want them to appear in on the user form.
Depending on your answers, I'll put together a way forward.

ps.
I'll await for you to get back to me with eager anticipation
I promise, I won't be getting back to you with eager anticipation!
I expect you meant to say: "I'll await with eager anticipation for you to get back to me";)
 
Pascal,
LOL.

Please find attached Prebuild list.

I take it once the code is established, I can add groups and prebuild items. To list all would just be too many to expect you to do.

Thanks again.
 

Attachments

  • Prebuild List.xlsx
    10.7 KB · Views: 1
I was hoping you'd set up the named ranges as actual named ranges too!
I take it once the code is established, I can add groups and prebuild items. To list all would just be too many to expect you to do.
Yes.

Important: What version of Excel are you using?
 
Back
Top