+ Reply to Thread
Results 1 to 8 of 8

Copy a range to an other sheet

  1. #1
    Registered User
    Join Date
    07-07-2014
    Location
    the Netherlands
    MS-Off Ver
    2016
    Posts
    56

    Copy a range to an other sheet

    I want to copy a range of cells from one sheet to an other sheet.

    Why is this not working?

    Sheets("Map1").Range(Cells(5, 5), Cells(10, 5)).Copy _
    Destination:=Sheets("Map2").Range(Cells(5, 5))
    Kind regards

    Jan (John)

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Copy a range to an other sheet

    Because mostlikely Map2 is anohter workbook and not another sheet as (in dutch) sheetnames are Blad1 and Blad2 where map1 and map2 are default indication of different workbooks.

    so unless yours sheets are called map1 and map2 and are within the same workbook it will not work.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Copy a range to an other sheet

    Try to specify the worksheets
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  4. #4
    Registered User
    Join Date
    07-07-2014
    Location
    the Netherlands
    MS-Off Ver
    2016
    Posts
    56

    Re: Copy a range to an other sheet

    Dear YasserKhalli

    It works! Thanks a lot.

    I have still one question: How do I (horizontal)center the data in the sh worksheet?

    Kind regards

    Jan

    PS. I managed already.
    Tread can be colosed
    Last edited by Jan Lichtenbelt; 02-21-2019 at 12:12 PM.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Copy a range to an other sheet

    You can simply record a macro to get the lines of aligning to center. Try it. It is so easy

  6. #6
    Registered User
    Join Date
    07-07-2014
    Location
    the Netherlands
    MS-Off Ver
    2016
    Posts
    56

    Re: Copy a range to an other sheet

    I hve still one problem, which I do not understand. I have the following subroputine for alignment of a column "Id" in sheet "Blad2".

    Sub TestAlignment()
    Dim wsTo As Worksheet
    Dim HeaderTo As String
    Dim SheetNameTo As String
    Dim Alignment As Integer

    On Error GoTo Fout

    HeaderTo = "Id"
    SheetNameTo = "Blad2"
    Alignment = 0

    Set wsTo = ThisWorkbook.Worksheets(SheetNameTo)

    'The range marked as "Id" is a single cell in sheet Blad2
    wsTo.Range(HeaderTo).Select
    Select Case Alignment
    Case 0
    Range(Selection, Selection.End(xlDown)).HorizontalAlignment = xlLeft
    Case 1
    Range(Selection, Selection.End(xlDown)).HorizontalAlignment = xlCenter
    Case 2
    Range(Selection, Selection.End(xlDown)).HorizontalAlignment = xlRight
    Case Else
    End Select

    Exit Sub
    Fout:
    MsgBox Err.Number & " " & Err.Description

    End Sub
    If I use this subroutine in the module (F5), it works perfectly. However, if I call this subroutine as results of the action on pushing a active button (part of sheet "Blad1") it fails. I get errornumber 9, something like "Subcript out of range".

    Why the different behavoir? What can I do that the subroutine works in both cases.

    Kind regards

    Jan (John)

  7. #7
    Registered User
    Join Date
    07-07-2014
    Location
    the Netherlands
    MS-Off Ver
    2016
    Posts
    56

    Re: Copy a range to an other sheet

    I make the problem more readable (I hope), I made a Excel file which shows the problem more clear. I hope someone can help me.

    Kind regards

    Jan
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-07-2014
    Location
    the Netherlands
    MS-Off Ver
    2016
    Posts
    56

    Re: Copy a range to an other sheet

    NO one can help?

+ 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. [SOLVED] Vba copy range from sheet 1 and paste values within a range on sheet 2
    By A440 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-27-2017, 10:23 AM
  2. [SOLVED] Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat
    By cjtimmer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2015, 06:27 PM
  3. copy range from one sheet to another and then copy 5 times skipping ten rows
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2015, 01:35 PM
  4. [SOLVED] Copy and paste range as values to specific sheet/range
    By lukestkd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2014, 09:48 PM
  5. Creating a copy button to copy range of cells to a different sheet
    By 5degrees in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-15-2013, 12:21 PM
  6. [SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range
    By g1eagle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2012, 01:36 PM
  7. Copy dynamic range from previous sheet and paste after last used row in a range
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-04-2010, 06:24 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