View original (French)

Copy/Paste Dropdown List Function

Koalacid Posted messages 70 Status Member -  
Koalacid Posted messages 70 Status Member -
Hello,

This is not my first question on this forum and I want to thank you for your help and the many hours (days?) of confusion you’ve spared me.

I’m back with a new challenge:

The story begins with a macro file (let’s call it file M) that has macro buttons and a dropdown list including the months of the year. On the other hand, there are about twenty files (let’s call them files X, Y, Z, ...) eager to know the month selected in the dropdown list to perform copy/pastes of values arranged in columns.

And here’s the goal. I select the month in the dropdown list in file M and run the macro that takes into account the selected month, opens files X, Y, Z (one by one, let’s not be too greedy), selects the "by year" tab, looks for the column following the column of the selected month (and yes, there's a little trick here, but with the offset function, I think it's just a detail), then selects that column, copies/pastes it into another tab (Update) of the same file, and then calmly goes to do the same in the next file.

So here it is, as much as I have some ideas on the functions to use (like offset or a simple copy-paste). But I haven't found anything online that comes close to what I want to do to be able to draw inspiration from it. There, I am in total fog.

I can roughly conceive the macro like this:

1) open the first destination file
2) check the dropdown list in file M and select the column +1 in the destination file
3) copy/paste this column into a pre-prepared tab
4) repeat

I will manage everything concerning the closing of the destination file after the copy/paste, and other details that are not directly related to this copy/paste story based on the dropdown list.

My first question is: is it possible? (yes, sometimes I doubt it)
And if so, how?

As usual, the goal is always to learn. So feel free to talk to me with lots of details so I can understand the lines of code and what’s happening.

Best regards,

12 answers

  1.
    ThauTheme Posted messages 1564 Status Member 160
     
    Hello Koalacid, hello forum,

    Yes, that should be possible, but we would need at least two example files. One that contains the macro (the source file) and a destination file. Just with some data so we can see the structure and code with a database very similar to the original file. Then we need to know the full path of the different files. If they are all in the same folder as the source file, it's not necessary...

    Looking forward to your reply.

    --
    See you,
    Thautheme
    0
  3. ThauTheme Posted messages 1564 Status Member 160
     
    Good evening KoalaCid, good evening everyone,

    I'm sorry, but today I didn't have the time. I promise to look into it first thing tomorrow...

    --
    See you,
    Thautheme
    0
  4. ThauTheme Posted messages 1564 Status Member 160
     
    Hello Koakacid, hello forum,

    Attached is your modified file with the code below: 
    Sub Macro1()
    Dim CS As Workbook 'declares the variable CS (Source Workbook)
    Dim MR As String 'declares the variable MR (Month Searched)
    Dim SF As Object 'declares the variable SF (File System)
    Dim DT As Object 'declares the variable DT (Working Folder)
    Dim FS As Object 'declares the variable FS (Files)
    Dim F As Object 'declares the variable F (File)
    Dim CD As Workbook 'declares the variable CD (Destination Workbook)
    Dim O1 As Object 'declares the variable O1 (Sheet 1)
    Dim O2 As Object 'declares the variable O2 (Sheet 2)
    Dim R As Range 'declares the variable R (Search)
    Dim CP As Byte 'declares the variable CP (Promo Column)
    Dim CNP As Byte 'declares the variable CNP (Non-Promo Column)

    Application.ScreenUpdating = False 'hides screen refreshing
    Set CS = ThisWorkbook 'defines the source workbook CS
    MR = CS.Sheets("Liste Déroulante").Range("D4").Value 'defines the month searched
    Set SF = CreateObject("Scripting.Filesystemobject") 'defines the file system SF
    Set DT = SF.GetFolder(ThisWorkbook.Path) 'defines the working folder DT
    Set FS = DT.Files 'defines the collection of files FS contained in the working folder
    For Each F In FS 'loops through all files F in the collection FS
     If Right(F.Name, 5) = ".xlsx" Then 'condition 1: if the file extension is ".xlsx"
     On Error Resume Next 'error handling (in case of error, proceed to the next line)
     Set CD = Workbooks(F.Name) 'defines the destination workbook CD (generates an error if this workbook is not open)
     If Err <> 0 Then 'condition 2: if an error has occurred
     Err.Clear 'clears the error
     Workbooks.Open (F) 'opens the file F
     Set CD = ActiveWorkbook 'defines the destination workbook CD
     End If 'end of condition 2
     Set O1 = CD.Sheets("Vol Prev") 'defines the sheet O1 (generates an error if this sheet does not exist)
     If Err <> 0 Then 'condition 3: if an error has occurred
     Err.Clear 'clears the error
     CD.Close 'closes the workbook CD
     GoTo suite 'goes to label "suite"
     End If 'end of condition 3
     On Error GoTo 0 'cancels error handling
     Set O2 = CD.Sheets("Update Vol") 'defines the sheet O2
     Set R = O1.Rows(5).Find(MR, , xlValues, xlWhole) 'defines the search R
     If Not R Is Nothing Then 'condition 4: if at least one found occurrence exists
     CNP = R.Column 'defines the non-promo column CNP
     CP = R.Column + 1 'defines the promo column CP
     Else 'otherwise
     MsgBox "Month not found!": Exit Sub 'message, exits the procedure
     End If 'end of condition 4
     O1.Range(O1.Cells(8, CP), O1.Cells(18, CP)).Copy 'copies the values from the promo column of the searched month in sheet O1
     O2.Range("C8").PasteSpecial (xlPasteValues) 'pastes the values into cell C8 of sheet O2
     O2.Range("B8:B18").Copy 'copies the calculated data from the range B8:B18 of sheet O2
     O1.Cells(8, CNP).PasteSpecial (xlPasteValues) 'pastes the values into row 8 of the non-promo column of the searched month
     Application.CutCopyMode = False 'removes the blinking of cells related to "copy"
     CD.Close SaveChanges:=True 'closes the destination workbook while saving the changes
     End If 'end of condition 1
    suite: 'label
    Next F 'next file in the collection of files FS
    Application.ScreenUpdating = True 'shows screen refreshing
    End Sub


    Basically, the macro does the following things:
    * loops to open all the files in the same folder as the source.xlsm file with a .xlsx extension. Check if this criterion fits?
    * once the file is opened, if it does not contain a sheet named Vol Prev, it is closed and the next file is processed...
    * otherwise, it copies the promo column of the searched month and pastes it into cell C8 of the Update Vol sheet.
    * It then copies the range B8:B18 from this same sheet to paste it into the non-promo column of the searched month.
    * Finally, it saves the destination workbook and closes it.
    * Then it goes to the next file...

    The only problem I encountered is that in your example, the calculations do not work? Do you have an explanation?
    I recommend creating a test folder where you will place the attached file and a copy of one or two destination files. Run it and see where it fails...

    Ugh! I’m new to this forum and I don’t know how to attach a file. Can you explain that to me even though normally the code is enough...

    --
    See you later,
    Thautheme
    0
    1. Koalacid Posted messages 70 Status Member 2
       
      Hello ThauTheme,

      First of all, thank you for your help! That's some code!

      Here is the link I used for the files.

      https://www.cjoint.com/

      I will do the tests this afternoon and try to understand as much as possible (I hope to understand everything, being optimistic by nature :) ). In the meantime, I have made some progress on this issue and discovered an alternative solution that simplifies the process. However, I will still need your code, don't worry.


      Best regards,
      0
  7. Koalacid Posted messages 70 Status Member 2
     
    Hello ThauTheme,

    I rewrote and adapted the macro according to the names of the tabs and the relevant ranges.

    It works without bugs but cannot find the month. I don't understand why.

    The error message: Month not found that the macro triggers in this case appears every time.

    Where could the error come from? On which tab does the code initiate the month search?

    Best regards,
    0
  8. ThauTheme Posted messages 1564 Status Member 160
     
    Hello koalacid, hello forum

    Yes, I forgot to warn you, sorry. I realized that the list of months in the Drop-down List tab did not match the months in the destination file of the example due to the space between P and 14. I added a space in the 12 cells for it to work. The names in the list must be identical to the names contained in row 5 of the Vol Prev folder. I hope you have been consistent across all files and that it’s the same everywhere with the same structure...

    I await your response...

    --
    See you,
    Thautheme
    0
  9. Koalacid Posted messages 70 Status Member 2
     
    FOUND IT!

    The error was definitely very simple. Between the files I sent you and the ones I use for the macro, there are differences (due to confidentiality I had to create new files).

    As a result, there was a difference in the line where the macro was looking for the month. I changed that line and guess what?

    Well, it works!

    ThauTheme, I worship you!

    Now all that's left is to properly integrate the macro into my files. Thank you for your help!

    Before I go, I just have one last question. What is the purpose of inserting these lines at the beginning:

    "Dim as...."

    Best regards,
    0
    1. Koalacid Posted messages 70 Status Member 2
       
      Finally, I'd like to clarify my question:

      How does this description of the macro variables work?
      If I wanted to change something like the source workbook to make it another Excel file, do I need to do that in this location?
      0
    2. Koalacid Posted messages 70 Status Member 2
       
      J have another question. If I want to change the definition of a tab, for example if I want O2 not to be on the destination workbook but on the source workbook (the one with the dropdown list), can I delete the part of the code that defines O2 as a tab called Update Vol and add a line at the very beginning?
      Such as:

      Set O2 = CS.Sheets("Machèremaman") 'defines tab 2
      0
  10. Koalacid Posted messages 70 Status Member 2
     
    Hello, I told you that I had modified the process to make it more coherent.

    Here is the new code that should work (I'll let you guess the rest...): 

    Sub Macro1()

    Dim CS As Workbook 'declares the variable CS (Source Workbook)
    Dim MR As String 'declares the variable MR (Month Searched)
    Dim SF As Object 'declares the variable SF (File System)
    Dim DT As Object 'declares the variable DT (Working Folder)
    Dim FS As Object 'declares the variable FS (Files)
    Dim F As Object 'declares the variable F (File)
    Dim CD As Workbook 'declares the variable CD (Destination Workbook)
    Dim O1 As Object 'declares the variable O1 (Sheet 1)
    Dim O2 As Object 'declares the variable O2 (Sheet 2)
    Dim R1 As Range 'declares the variable R1 (Search 1)
    Dim R2 As Range 'declares the variable R2 (Search 2)
    Dim CP As Byte 'declares the variable CP (Promo Column)
    Dim CNP As Byte 'declares the variable CNP (Non-Promo Column)

    Application.ScreenUpdating = False 'hides screen refreshes
    Set CS = ThisWorkbook 'sets the source workbook CS
    Set O1 = CS.Sheets("Vol Non Promo") 'sets the sheet O1
    Set R1 = O1.Rows(4).Find(MR, , xlValues, xlWhole) 'sets the search R1
    If Not R1 Is Nothing Then 'condition A: if there is at least one occurrence found
     CNP = R1.Column 'sets the non-promo column CNP
    Else 'otherwise
     MsgBox "Month not found!": Exit Sub 'message, exit the procedure
    End If 'end of condition A
    MR = CS.Sheets("Macro").Range("G10").Value 'sets the month searched
    Set SF = CreateObject("Scripting.Filesystemobject") 'sets the file system SF
    Set DT = SF.GetFolder(ThisWorkbook.Path) 'sets the working folder DT
    Set FS = DT.Files 'sets the collection of files FS contained in the working folder
    For Each F In FS 'loop through all files F in the collection FS
     If Right(F.Name, 5) = ".xlsm" Then 'condition 1: if the file extension is ".xlsm"
     On Error Resume Next 'error handling (if an error occurs, proceed to the next line)
     Set CD = Workbooks(F.Name) 'sets the destination workbook CD (throws an error if this workbook is not open)
     If Err <> 0 Then 'condition 2: if an error has occurred
     Err.Clear 'clears the error
     Workbooks.Open (F) 'opens the file F
     Set CD = ActiveWorkbook 'sets the destination workbook CD
     End If 'end of condition 2
     Set O2 = CD.Sheets("Vol_SKU_COLS_SAISI") 'sets the sheet O2 (throws an error if this sheet does not exist)
     Set R2 = O2.Rows(4).Find(MR, , xlValues, xlWhole) 'defines the search on sheet 02
     If Not R2 Is Nothing Then 'condition B: if there is at least one occurrence found
     CNP = R2.Column 'sets the non-promo column CNP
     CP = R2.Column + 1 'sets the promo column CP
     If Err <> 0 Then 'condition 3: if an error has occurred
     Err.Clear 'clears the error
     CD.Close 'closes the workbook CD
     GoTo suite 'go to the label "suite"
     End If 'end of condition 3
     On Error GoTo 0 'cancels error handling
     O1.Range(O1.Cells(7, CNP), O1.Cells(86, CNP)).Copy 'copies the values from the non-promo column of the searched month in sheet O1
     O2.Range(O2.Cells(7, CNP), O2.Cells(86, CNP)).PasteSpecial (xlPasteValues) 'pastes the values into the CNP column of sheet 02
     Application.CutCopyMode = False 'removes the flickering of cells related to "copying"
     CD.Close SaveChanges:=True 'closes the destination workbook saving changes
     End If 'end of condition 1
    suite: 'label
    Next F 'next file in the collection of files FS
    Application.ScreenUpdating = True 'displays the screen refreshes
    End Sub


    The process is the following. Instead of copying from a source file to a file in a sheet where a calculation leads to a new result to be copied into sheet B (reduced schematic to its elementary simplicity of what I said earlier), here is the new process.

    I take the information from all the files and paste it into a source file, do my calculations and get the final result that I therefore need to paste according to the month. This is when the code written in this post comes into play.

    Sharp observer that you are, you will notice the subtle touch of your code. (almost everything if not everything). I just flipped the sheet story.

    Anyway. After re-re-re-reading the code, I run the macro with a confident F5.

    ----> Pèf error on the last line: 

    Next F 'next file in the collection of files FS

    An error message: "Next without For"

    After checking, there wasn’t one in your code. How did you manage to corrupt your VBE without this "for"????

    I’m drowning....

    Best regards,
    0
  11. ThauTheme Posted messages 1564 Status Member 160
     
    Good evening Koalacid, good evening everyone,

    I'm also swimming because without being able to test with two example concrete files (you can anonymize without changing the structure), it's hard to understand where you're going with this.

    I go back to your code...
    * First point:
    you're starting a search R1 while the month MR you're looking for isn't defined yet? Crash guaranteed MR=""
    Solution, move the line that defines the month before the search: 
    Application.ScreenUpdating = False 'hides screen refreshes
    Set CS = ThisWorkbook 'defines the source workbook CS
    MR = CS.Sheets("Macro").Range("G10").Value 'defines the month being searched
    Set O1 = CS.Sheets("Vol Non Promo") 'defines tab O1
    Set R1 = O1.Rows(4).Find(MR, , xlValues, xlWhole) 'defines search R1

    * Second point:
    This search allows you to define the variable CNP which you're redefining later with search R2.
    In this case, what's the point of the first search R1?

    * Third point:
    You've changed the extension of the files to search. So the file containing the macro is also included in the list, but it shouldn't be taken into account. I added the line below to address this issue: 
    If F.Name = CS.Name Then GoTo suite


    * Fourth point:
    It's not a Next that's missing, but an End IF at the end of:
    If Not R2 Is Nothing Then...

    * Fifth point:
    you define the variable CP but you don't use it?

    So, here’s the modified code. Since the non-promo column CNP is defined twice, I removed the second definition. You will adjust... 

    Sub Macro1()
    Dim CS As Workbook 'declares variable CS (Source Workbook)
    Dim MR As String 'declares variable MR (Month Searched)
    Dim SF As Object 'declares variable SF (File System)
    Dim DT As Object 'declares variable DT (Working Folder)
    Dim FS As Object 'declares variable FS (Files)
    Dim F As Object 'declares variable F (File)
    Dim CD As Workbook 'declares variable CD (Destination Workbook)
    Dim O1 As Object 'declares variable O1 (Tab 1)
    Dim O2 As Object 'declares variable O2 (Tab 2)
    Dim R1 As Range 'declares variable R1 (Search 1)
    Dim R2 As Range 'declares variable R2 (Search 2)
    Dim CP As Byte 'declares variable CP (Promo Column)
    Dim CNP As Byte 'declares variable CNP (Non-Promo Column)

    Application.ScreenUpdating = False 'hides screen refreshes
    Set CS = ThisWorkbook 'defines the source workbook CS
    MR = CS.Sheets("Macro").Range("G10").Value 'defines the month being searched
    Set O1 = CS.Sheets("Vol Non Promo") 'defines tab O1
    Set R1 = O1.Rows(4).Find(MR, , xlValues, xlWhole) 'defines search R1
    If Not R1 Is Nothing Then 'condition A: if at least one occurrence is found
     CNP = R1.Column 'defines the non-promo column CNP
    Else 'otherwise
     MsgBox "Month not found!": Exit Sub 'message, exit procedure
    End If 'end of condition A
    Set SF = CreateObject("Scripting.Filesystemobject") 'defines the file system SF
    Set DT = SF.GetFolder(ThisWorkbook.Path) 'defines the working folder DT
    Set FS = DT.Files 'defines the collection of files FS in the working folder
    For Each F In FS 'loop through all files F in the collection FS
     If Right(F.Name, 5) = ".xlsm" Then 'condition 1: if the file extension is ".xlsm"
     If F.Name = CS.Name Then GoTo suite 'if the file is this file, go to label suite
     On Error Resume Next 'error handling (skip to the next line in case of error)
     Set CD = Workbooks(F.Name) 'defines the destination workbook CD (generates an error if this workbook is not open)
     If Err <> 0 Then 'condition 2: if an error was generated
     Err.Clear 'clears the error
     Workbooks.Open (F) 'opens file F
     Set CD = ActiveWorkbook 'defines the destination workbook CD
     End If 'end of condition 2
     Set O2 = CD.Sheets("Vol_SKU_COLS_SAISI") 'defines tab O2 (generates an error if this tab does not exist)
     If Err <> 0 Then 'condition 3: if an error was generated
     Err.Clear 'clears the error
     CD.Close 'closes the destination workbook CD
     GoTo suite 'go to label "suite"
     End If 'end of condition 3
     On Error GoTo 0 'cancels error handling
     Set R2 = O2.Rows(4).Find(MR, , xlValues, xlWhole) 'defines the search on tab 02
     If Not R2 Is Nothing Then 'condition B: if at least one occurrence is found
     CP = R2.Column + 1 'defines the promo column CP
     End If
     O1.Range(O1.Cells(7, CNP), O1.Cells(86, CNP)).Copy 'copies the values from the non-promo column of the searched month in tab O1
     O2.Range(O2.Cells(7, CNP), O2.Cells(86, CNP)).PasteSpecial (xlPasteValues) 'pastes the values into the CNP column of tab 02
     Application.CutCopyMode = False 'removes the cell flashing related to "copy"
     CD.Close SaveChanges:=True 'closes the destination workbook saving changes
     End If 'end of condition 1
    suite: 'label
    Next F 'next file in the collection of files FS
    Application.ScreenUpdating = True 'shows screen refreshes
    End Sub


    --
    See you later,
    Thautheme
    0
  13. ThauTheme Posted messages 1564 Status Member 160
     
    Good evening Kolacid, good evening everyone,

    You haven't addressed any of the points I raised in post #13. You're passing the buck and telling me to deal with it myself. That's not how I see things... If you want help, you need to provide explanations!

    --
    See you,
    Thautheme
    0
    1. Koalacid Posted messages 70 Status Member 2
       
      Haven't had the time to take care of it yet, it's coming.
      0
    2. ThauTheme Posted messages 1564 Status Member 160
       
      I'm waiting...
      0
    3. Koalacid Posted messages 70 Status Member 2
       
      Once again, thank you for your help Thautheme, unfortunately this issue, while not resolved, may take some time and I don't want to take advantage of yours.


      I will manage to find a solution later when I can take care of it.


      Thanks again for your quick and very interesting responses,


      Sincerely,
      0