0

I have the following macro that loops through a list of clients and saves individual workbooks for each client in a specific file location. The issue that is going over my head is I want to only save one particular worksheet in the workbook per client and not all of the tabs from the workbook.

Here is the entire macro:

Sub ClientDataRefresh()

    With ThisWorkbook.Worksheets("Output")

    Dim dvCell As Range
    Dim inputRange As Range
    Dim c As Range
    Dim i As Long

     'Cell that has data validation
    Set dvCell = ThisWorkbook.Worksheets("Output").Range("C5")
     'Determine where validation comes from
    Set inputRange = Evaluate(dvCell.Validation.Formula1)

    i = 1
     'Begin loop
     Application.ScreenUpdating = False
     For Each c In inputRange
     dvCell = c.Value
     ThisWorkbook.RefreshAll
     ThisWorkbook.Worksheets("Output").Range("A1:O10").Columns.AutoFit

    With ThisWorkbook.Worksheets("Template")
    LR = .Cells(Rows.Count, 7).End(xlUp).Row
    10: If .Cells(LR, 7) = "" Then LR = LR - 1: GoTo 10
    .PageSetup.PrintArea = "$A$1:$I$" & LR
    End With

     thisDate = Replace(Date, "/", "-")
     thisName = Sheets("Template").Range("H7").Text
     filePath = "C:\Users\nalanis\Dropbox (Decipher Dev)\Analytics\Sales\"
     Application.DisplayAlerts = False
     ThisWorkbook.Worksheets("Template").Select
     ThisWorkbook.Worksheets("Template").Copy
     ThisWorkbook.Worksheets("Template").SaveAs Filename:=filePath & thisName & " " & "Usage Report" & " "  & thisDate & ".xlsx", FileFormat:=xlOpenXMLWorkbook
     Application.DisplayAlerts = True
     ActiveWorkbook.Close
    Next c

    End With


End Sub

I have tried looking and applying different potential solutions but no such luck.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
naa34
  • 5
  • 2
  • Is the worksheet you want to save same for all clients or does it change? Eg. Is it always "Template" tab? Can you delete all the other tabs just before saving? – Aneta Jun 05 '18 at 15:21
  • See [this question](https://stackoverflow.com/questions/20246465/how-to-copy-only-a-single-worksheet-to-another-workbook-using-vba). After you've created a new workbook with `ThisWorkbook.Worksheets("Template").Copy`, you don't reference that new workbook when trying to save it - you're still referencing `ThisWorkbook` when you want `ActiveWorkbook`. – BigBen Jun 05 '18 at 15:43
  • Hi @Aneta, yes that is correct. It's always the template tab. The data changes for each client during each refresh but that is the tab I use. – naa34 Jun 05 '18 at 16:27
  • Hey @BigBen so I made the following change: ThisWorkbook.Worksheets("Template").SaveAs -> ActiveWorkbook.Worksheets("Template").Save As. What I see now is that the last client's data on my loop is being saved for all of my clients. I came across this yesterday and wasn't sure how to resolve it. – naa34 Jun 05 '18 at 16:44
  • 1. You don't need `Worksheets("Template")`... - [Workbook.SaveAs](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel). 2. Without your file in front of me, it's hard to say what's causing that issue. – BigBen Jun 05 '18 at 16:55
  • @BigBen it would be great if I could provide it but I have a direct connect to SQL Server so it will fail as the macro is executed. – naa34 Jun 05 '18 at 17:00

1 Answers1

1

This is the code i've used to save only 1 worksheet as a .csv file. Basically it removes every sheet other than the sheet I want ("Pending") and saves it as .csv, but then closes the original workbook without saving, as to not save with all the deletions I did.

Dim excelObject As Object
Dim objExcel As Object
Dim sheet As Object
Dim csvFile As String
xlFile = "C:\Users\PathName.xlsx"
csvFile = Left(xlFile, InStrRev(xlFile, ".")) & "csv"

'open excel file
Set objExcel = CreateObject("Excel.Application")
Set excelObject = objExcel.Workbooks.Open("C:\Users\PathName.xlsx")
'Recognize the sheet we are looking to import
Set sheet = excelObject.Worksheets("Pending")
'suppresses dialog boxes when deleting worksheets
excelObject.Application.DisplayAlerts = False
Dim wsName As String

'Delete all sheets except the one sheet we want
wsName = "Pending"

For Each Sh In excelObject.Worksheets
    If Sh.Name <> wsName Then
        Sh.Delete
    End If
Next Sh

'Save the file as a CSV but do not save the original workbook we edited
objExcel.ActiveWorkbook.SaveAs csvFile, FileFormat:=6, CreateBackup:=False
excelObject.Close

'Closes out of excel and removes it from the computer memory
objExcel.Quit
Set objExcel = Nothing
TBoulz
  • 339
  • 5
  • 20