View original (French)

Retrieve values from an Excel column.

Solved
roberto93 Posted messages 14 Status Member -  
azitech Posted messages 1 Status Member -
Bonjour,

I want to retrieve the values from a column in sheet 1 and send them to sheet 2.
I have 12 sheets, and I want to automate the process to be faster because I need to do the same thing on dozens of workbooks.
Sheet 1 groups data for the entire year, each month is in a column

Column A Column B Column C Column D and so on...
Month Activity Duration of the activity Month

I want to retrieve the data from the month columns only from sheet 1 and copy/paste it onto sheets 2, 3, 4...12
in cell N23 on all the sheets.

Here is the macro I have so far

Sub Macro2()

Selection.Copy
Sheets(ActiveSheet.Name).Select
Range("N23").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

I deduce that I need to add an incrementing variable to read the desired columns and retrieve the values.
For a column, it's not the same as for rows; I can't do i = i +1, I need to use ASCII code and do a double loop for columns with two letters. How should I proceed?
In ASCII code, A = 65
I need your help to solve my problem
Thank you in advance
Configuration: Windows 2000 Internet Explorer 6.0

4 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    you can use a simple and easy-to-write-and-follow code

    Sub Macro1()
    '
    Sheets("Sheet1").Select
    Columns("A:A").Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste

    Sheets("Sheet1").Select
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    ActiveSheet.Paste

    ' add code here for the twelve sheets

    Sheets("Sheet1").Select
    Range("A1").Select
    Application.CutCopyMode = False
    End Sub

    See you!
    2
    1. roberto93 Posted messages 14 Status Member
       
      Thank you for your help, I used your method, it is sufficient for me

      However, is there a way to create a loop, let's imagine we had more than 100 columns to copy. How should we proceed?

      Best regards,
      -1
  2. pitchoooo
     
    Hello
    I had created this macro to search for a cell that contained a certain string of characters here ------Application----- and I deleted all the cells afterwards if that helps you.

    Sub appli()
    Dim i As Long
    Dim j As Long
    Dim k As String
    Dim kk As String
    i = 1
    jj = 65
    j = 65
    k = Chr(j)
    kk = Chr(jj)
    For jj = 65 To 72 Step 1
    kk = Chr(jj)
    For j = 65 To 90 Step 1
    k = Chr(j)
    'Do While ActiveSheet.Range(kk & k & i).Value <> ""
    'For jj = 65 To 73 Step 1
    'For j = 65 To 90 Step 1
    Do While i <> 1000

    If ActiveSheet.Range(kk & k & i).Value = " ------------Applications (WMI)---------" Then
    ActiveSheet.Range(kk & k & i & ":IV" & i).Delete Shift:=xlToLeft
    End If
    i = i + 1
    Loop
    i = 1
    Next
    'Loop
    Next

    End Sub

    There you go
    ++
    1
  3. azitech Posted messages 1 Status Member 1
     
    Il semble qu'il y ait quelques erreurs typographiques dans votre question. Si vous souhaitez savoir comment coller un chiffre dans une colonne plusieurs fois dans un programme comme Excel, vous pouvez le faire en suivant ces étapes :

    1. Copiez le chiffre que vous souhaitez coller.
    2. Sélectionnez la colonne ou la plage de cellules où vous voulez coller le chiffre.
    3. Appuyez sur `Ctrl` + `V` pour coller le chiffre dans toutes les cellules sélectionnées.

    Si vous utilisez un autre logiciel ou avez besoin d'une méthode spécifique, merci de préciser.
    1
  4. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    On the same sheet, it is possible to write the code differently, but in your case, you need to take a different column from Sheet1 to paste it into a different sheet each time. However, it is possible to name each column of Sheet1, and instead of writing in the macro

    Columns("A:A").Copy
    we could write the name we gave to this column, for example, a.copy or Pierre.copy, etc.

    Sheets("Sheet1").Select ("Sheet1") is the name of the main sheet
    Columns("A:A").Copy this line refers to column A
    Sheets("Sheet2").Select ("Sheet2") is the name of a reception sheet
    Range("A1").Select A1 is the starting position to paste the info
    ActiveSheet.Paste and paste

    from this code, you can write this function for each column that you need to copy to the sheet of your choice

    Cheers

    __________________________________________________________________

    Need help. In the forum, volunteers take the time to decipher your sometimes confusing explanations and offer their knowledge.
    If a solution is found, please have the courtesy to mark your status as resolved. This way, your problem will be classified and serve as a reference for other similar cases.
    -1