View original (French)

Copy a table to a new sheet

Solved
NLAH Posted messages 135 Status Member -  
NLAH Posted messages 135 Status Member -
Hello,

I am a beginner in VBA, and I would like to create a macro that:
pastes a table onto a new sheet.

Sincerely,

Configuration: Windows 7 / Chrome 52.0.2743.116

1 answer

  1. Anonymous user
     
    Hello nihadlahit,

    You are not providing enough information!

    1) For the source (your table to copy):

    a) Name of the sheet where it is located: Sheet1? something else?
    b) Range of cells of your table (including any
    header row; including any total row);
    example: "B4:G200"

    2) For the destination:

    a) Is the sheet already present, and what is its name? For example:
    Sheet2? something else? If the destination sheet is not already present,
    then it needs to be added; it could be Sheet2, but
    maybe you prefer to give it another name? If so, what?

    b) Imagine that your table is already copied; what is the cell
    in the top left corner? Because it will be from this cell that the
    copying will take place (downward for rows and to the right for columns).

    Looking forward to hearing from you, best regards. ????
    0
    1. NLAH Posted messages 135 Status Member 2
       
      I agree, I actually want to copy a table with a variable number of rows, this table is on 'Sheet 1', I want to paste it on a new sheet 'Sheet 2', which I will add automatically.
      0
    2. Anonymous user > NLAH Posted messages 135 Status Member
       

      It's already better! But even if your table has a variable number of rows:
      what is the 1st row (including header row)? And from which column
      to which column? If the number of columns is also variable, indicate what
      is the 1st column! See you soon 😊
      0
    3. NLAH Posted messages 135 Status Member 2
       
      ok, my table starts from the second line:
      H2:L(Rows.count)
      0
    4. Anonymous user > NLAH Posted messages 135 Status Member
       

      Here is the VBA code: 

       Option Explicit Sub Essai() ' At the start, your workbook has only one sheet named "Feuil1", ' so you are necessarily on it when you run this macro; ' last row of your table, according to the 1st column H Dim dlig As Long: dlig = Range("H" & Rows.Count).End(xlUp).Row ' Adding a new sheet to the right of the 1st one in the workbook ' (so Feuil1), which will be automatically named Feuil2 Worksheets.Add , Worksheets(1) ' You forgot to indicate what the destination on Feuil2 is! ' I will assume for example that it's starting from B2; it's up to you ' to change it if necessary. Worksheets("Feuil1").Range("H2:L" & dlig).Copy [B2] End Sub

      Feel free to ask me for more info if needed.
      Best regards.  😊
      0
    5. Anonymous user > NLAH Posted messages 135 Status Member
       
      "No news, good news": if you have succeeded and don't need anything else, please go to the top of the page to click on the link "Mark as resolved". Best regards. ????
      0