Excel VBA Looping in sheet and saving every looped file based on cell range

Question

Anyone,

I'm trying to make a program in excel vba in which the macro would look/loop for the sheet name in the workbook base on the excel range. Also, after looking for the sheet name, the program would save the sheet based on the given file name on the other cell range.

My main problem here is on how I can save the loop file/sheet name based on the teritory name given in the picture provided below.

Hope you can help me with my problem.

Here's my recent work on the macro, I can save the file but it saves the file based on the sheet name I have looked up. Thanks. sample picture here

Sub Save_Test()
Dim ws As Worksheet
Dim wb As Workbook
Dim c, b As Range
Dim rng, rng2 As Range
Dim mysheet As Worksheet
Dim LastRow, LastRow2 As Integer
Dim file_name As String

LastRow = Range("I" & rows.Count).End(xlUp).row

Set rng = Range("J5:J" & LastRow)



Set ws = Worksheets("Control")
For Each c In rng

    Sheets(c.Value).Select
    Cells.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Application.DisplayAlerts = False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    ActiveSheet.Name = c.Value
    Application.CutCopyMode = False
    ActiveWindow.DisplayGridlines = False
    TemplateLocation = ThisWorkbook.Path

    file_name = c.Value
    ActiveWorkbook.SaveAs Filename:=TemplateLocation & "\" & "Reports" & "\" & Format(Now() - 1, "mmyy") & " " & file_name & " Hustle Board thru " & Format(Now() - 1, "mm-dd-yy"), FileFormat:=51, CreateBackup:=False

    Application.DisplayAlerts = False
    ActiveWindow.Close

Next
Sheets("Control").Select
End Sub

A good question here is one which preferably includes some code, or at the very least a clear description of a *specific* problem, and not just a specification. Pick the first specific part of this you have a problem with tackling, and ask about that. — Tim Williams, Jun 30 '16 at 20:47
Please review [How to Ask](http://stackoverflow.com/help/how-to-ask) — Scott Holtzman, Jun 30 '16 at 20:50
Sorry for the late upload of my code. There you go. I have already put my recently working code on saving the files. All I want is to have the filename based on the specific cell range and not based on the sheet name. — Mark, Jun 30 '16 at 20:51
I highly suggest reading, and implementing, [how to avoid using `.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), it will save you headaches and also help the code run a little faster. — BruceWayne, Jun 30 '16 at 21:23

KyloRen · Accepted Answer · 2016-07-01T04:00:38.123

You will have to fill in the other stuff you need to do, but going off your picture and you code, this should get you the value in the teritory column

  Dim r As Range
  Dim rng As Range
  Dim LastRow As Long
  Dim ws As Worksheet

  LastRow = Range("I" & Rows.Count).End(xlUp).Row

  Set rng = Range("J5:J" & LastRow)      
   For Each r In rng

        file_name = r.Offset(, -1)
          ActiveWorkbook.SaveAs Filename:=TemplateLocation & "\" & "Reports" & "\" & Format(Now() - 1, "mmyy") & " " & file_name & " Hustle Board thru " & Format(Now() - 1, "mm-dd-yy"), FileFormat:=51, CreateBackup:=False

   Next r
End Sub

BTW, if you did not already know, declaring varibales like this below is not good practice.

Dim rng, rng2 As Range

In this case rng is not a rng at this point. You need to do this below to explicitly declare as a Range variable.

Dim rng as Range, rng2 As Range

Then please mark it as the answer and or vote for it if it helped you. — KyloRen, Jul 02 '16 at 00:32

Excel VBA Looping in sheet and saving every looped file based on cell range

1 Answers1