+ Reply to Thread
Results 1 to 18 of 18

Copy from another sheet and paste into "active sheet"

  1. #1
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Copy from another sheet and paste into "active sheet"

    Hi Guys,
    I have the below code which works fine for me if the "Application.Goto Reference:" (which is what I use to insert a range of specific rows), is on the same sheet, into the active sheet.

    What I would like to do is copy a range from another specific sheet ("Lighting") and paste it into the selected location on the original sheet.

    The original sheet name is dynamic so once the code copies from the "GoTo" reference range, I need to redirect it back to the original sheet that kicked off the code.

    I hope that make sense.

    Appreciate your help.



    Sub Add_Prebuilds()
    '
    '

    Add_Prebuildsub:
    Dim Endpoint2 As Integer
    Dim StartPoint2 As Integer
    Dim Location2 As String
    Dim CellCheck As String

    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)
    Endpoint2 = StartPoint2 + 14
    Location2 = StartPoint2 + 1 & ":" & Endpoint2
    CellCheck = "A" & StartPoint2 + 1

    ' This confirms the section is being added in a safe location

    If StartPoint2 <= 20 Then GoSub LocationError
    If StartPoint2 = False Then Exit Sub
    If StartPoint2 <> False Then If Range(CellCheck) = 2 Then GoSub Insertsect
    GoSub LocationError

    Exit Sub

    Insertsect:
    ' This turns the protection off on the sheet

    ActiveSheet.Select
    ActiveSheet.Unprotect ("Password")

    'Select row place to add

    Rows(Location2).Select
    Selection.Insert

    ' Copy the template blank section
    Application.Goto Reference:=Worksheets("Lighting").Range("$38:$51")
    Selection.Copy

    ' Paste the template blank section
    Rows(Location2).Select
    ActiveSheet.Paste
    Selection.EntireRow.Hidden = False

    ' This turns the protection back on for the sheet

    ActiveSheet.Select
    ActiveSheet.Protect Password:="Password", DrawingObjects:=False, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
    True, AllowFiltering:=True, UserInterfaceOnly:=True
    Application.ScreenUpdating = True
    Exit Sub

    LocationError:
    Debug.Print Num
    MsgBox "ERROR : The Row number selected was not the last row in an existing section. Please select a valid row."

    ' This turns the protection back on for the sheet

    ActiveSheet.Select
    ActiveSheet.Protect Password:="Password", DrawingObjects:=False, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:= _
    True, AllowFiltering:=True, UserInterfaceOnly:=True
    Application.ScreenUpdating = True
    End Sub
    Last edited by Lazarus Rises; 09-21-2021 at 06:54 PM.

  2. #2
    Registered User
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    58

    Re: Copy from another sheet and paste into "active sheet"

    Try using sheets(1). This will work off index number not chaging sheet name.

    Or store Worksheet like

    Set ws=workobooks().activesheet

  3. #3
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    not working.

    I was hoping to after directing the code to the reference sheet and range to copy, to have a way to then activate the previous "active sheet" that I wanted to paste into.

    With the code as is, it copies the range but then stops.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,008

    Re: Copy from another sheet and paste into "active sheet"

    If you select any cell in the row where you want the insert/paste to start and run this, is that close to what you need?
    Change references where required and try it on a copy of your original.
    Please Login or Register  to view this content.
    You'll have to insert the unprotecting and protecting as I forgot.

    If you just want to paste at the bottom of the current data range, let us know.
    Last edited by jolivanes; 09-21-2021 at 11:50 PM.

  5. #5
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Jolivanes,
    i need to keep the start point and location code as this ensures a row or range doesn't get inserted in the wrong section as this would stuff up the sheet formulas.

    Appreciate your help.

  6. #6
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Just bumping this up.

    Is there a way to copy from another sheet and then redirect the code to the sheet that was active when the macro was initiated.

    The sheets have dynamic names so I can't just reference the sheet by name where the data is being pasted onto.

    Thanks.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,008

    Re: Copy from another sheet and paste into "active sheet"

    What was the result when you ran the code from Post #4? You never mentioned before that you had formulae in the sheet so that needs to be taken in consideration.
    Why would you need to change sheets to copy and paste?
    Explain in a concise manner what you need to accomplish.
    For me, adding 60+ lines of code that do not work is no help. Everybody does thing different.
    Re: "Is there a way to copy from another sheet and then redirect the code to the sheet that was active when the macro was initiated." This is what my code does.
    What are dynamic names? Changing names?

  8. #8
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Jolivanes,
    the intent is to have a sheet with pre-populated data that I would like to use as a template to insert within certain rows on any given active working sheet.

    The working sheets are able to be renamed, so I can't reference the working sheet name in the macro.

    At the moment the code I provided above, when directed to copy a range within the active working sheet works perfectly. However if the range to be copied is on another sheet (pre-populated data sheet "Lighting"), then it will paste on the pre-populated data sheet ("Lighting"), the same sheet it copying the range from, instead of returning to the active working sheet from which I initiate the macro.

    I want to be able from the active working sheet, to initiate the macro to copy a range on the "Lighting" 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 "new section" or the "prebuild" select row 35.

    The attached has two macros.

    1- Copies and inserts range from the same sheet
    2- Meant to copy a range from the "Lighting" sheet and paste back on sheet 1. Just remember sheet 1 could be renamed to anything else.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Bumping this up.

  10. #10
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Any love on this?

    Please.

  11. #11
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Is there a way to copy a range from another sheet and insert it into the active sheet from which you initiated the code?

    I have a sample file in a previous post above.

    Can somebody please help?

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,827

    Arrow Re: Copy from another sheet and paste into "active sheet"


    Yes via the Range.Copy method for example

  13. #13
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Marc,
    are you able to modify the code in the uploaded sheet above?

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,827

    Post Re: Copy from another sheet and paste into "active sheet"

    (removed as duplicate)
    Last edited by Marc L; 10-13-2021 at 09:00 PM.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,827

    Arrow Re: Copy from another sheet and paste into "active sheet"


    Maybe if you first well elaborate what should be done
    and after you add the link on the other forums where you have created the same thread

  16. #16
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet"

    Marc,
    I though I had elaborated what I was after already.

    With regard to posting on another forum, (https://chandoo.org/forum/threads/co...e-sheet.47043/) you are right, I have today, but only after not being able to get my query answered here. Is there an issue with asking another forum for help? More than happy to post the answer here once I have it, regardless of source.

    I don't know how else to say that I would like to copy a range from another sheet and insert it into the active sheet (that is the sheet I would be working on).

    I have provided the code I am using to insert a range from the same sheet.

    I would like the insert of a range from another sheet to work in the same way, except the range it is copying is on another sheet.

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,827

    Arrow Re: Copy from another sheet and paste into "active sheet"


    Give a clear enough example in order there is nothing to guess : what actions you do within which worksheet,
    which VBA procedure you call, well describe the before state and the expected result

  18. #18
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    68

    Re: Copy from another sheet and paste into "active sheet" - SOLVED

    As promised p45cal, from https://chandoo.org/forum/threads/co...e-sheet.47043/ came through with exactly what I was after.

    Solution below:


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Lazarus Rises; Yesterday at 05:45 PM. Reason: Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. many actions in one macro: copy sheet "G" and paste "g_koond" no empty rows and add ID
    By reinsermat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-05-2020, 04:28 AM
  2. [SOLVED] Copy Data from "Source" Sheet to "Target" Sheet (Code need help thanks)
    By mankit87 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-17-2019, 11:45 PM
  3. Looping thru rows and copy cell values from "Inputs" sheet to "Output" sheet
    By hariexcel1987 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2015, 11:32 PM
  4. [SOLVED] Copy sent to "History Sheet" everytime the Active X button is pressed.
    By Spreadlover in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-26-2013, 08:40 PM
  5. [SOLVED] Copy range from hidden sheet to active sheet if cell ="Some text"
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2013, 01:19 AM
  6. Replies: 3
    Last Post: 08-15-2012, 04:00 AM
  7. Macro to CUT & Paste a row to another sheet if "Active" appears in Column J
    By Fferjay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2011, 12:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1