Help needed with basic macro...

[Miguelinho87]

Hi everybody, I'm Miguel from Canada.

I'm new and it's my first time posting here.
So if I do something wrong, let me know...

I already wrote many basic macros at the office to ease my work load but I'm learning by myself.
This one needs to be done by Monday (it was a request) and I'm not be sure I'll be able to do it since I need to stay with my daughter at the hospital while she's having her leg surgery.
So I really need your help...

Here is what I'm trying to do and how the macro should go:

1) Press a button to start the macro (I know how to link a macro to a button so not a problem)

2) Check cells in range J2 to J350
If empty : Go to next cell
If not empty : Copy "Resource" file placed in a definite path, paste it in the same folder with "_1" at the end, rename it with the corresponding reference (same row, column A) and then go to next cell in column J to keep checking.

I know it looks pretty simple and I think I already wrote something similar in the past but I can't remember where...

If someone can help me with this.

Thank you so much for your help !!!

Miguel

 

[Miguelinho87]

HI, I managed to do what I wanted by writing it on a note and then sending it to someone to try it.
If someone knows who to close a thread, let me know...

Thank you !!!

 

[smozgur]

Hello, @Miguelinho87

Welcome to the MrExcel Message Board.

This one needs to be done by Monday (it was a request) and I'm not be sure I'll be able to do it since I need to stay with my daughter at the hospital while she's having her leg surgery.
So I really need your help...

Hope you and your daughter get to feeling better soon.

Threads are not closed on the board, but only useful answers could be marked as the solution to help future readers. Therefore, please leave it as is if you don't receive a working answer. You can also post your own answer and mark it as the solution of course. I removed the solution mark from your last post since it is not an actual answer. No need to select an answer that doesn't help in order to close the thread.

The following code, on the other hand, should do what you need as my approach (it could be solved in many different ways). You didn't specify the Resource file extension, or provided a name for the definite path, so, I used variables to define that information to use in the code to make them configurable as much as possible. You can change the variables and see if it would help. I also wrote comments, so you can follow the logic.

Sub doIt()
Dim strPath As String
Dim strFile As String
Dim strPathSuffix As String
Dim rng As Range
Dim cll As Range

    ' Parameters
    strPath = "C:\definitePath"
    strFile = "Resource"
    strFileExt = ".xlsx"
    strPathSuffix = "_1"
   
    ' Criteria range
    Set rng = ActiveSheet.Range("J2:J350")
   
    ' Loop through cells in the criteria range
    For Each cll In rng.Cells
        ' Check if the current cell is empty or not
        If Not IsEmpty(cll) Then
            ' Copy the certain file saved in the defined folder
            ' into the folder named with the defined prefix
            ' by renaming the file with the value in the corresponding A column cell
            FileCopy strPath & Application.PathSeparator & strFile & strFileExt, _
                     strPath & strPathSuffix & Application.PathSeparator & cll.EntireRow.Cells(1) & strFileExt
        End If
    Next cll
   
End Sub