1

I have a workbook that i want to save Specifc Sheets from into a new workbook. I want to change a cell value... say A1... in the first sheet for each instance of the new workbook save. I can get one iteration w/o any problems, but when the code goes to create the 2nd workbook copy using the 2nd value of A1, it overwrites the first saved workbook A1 cell with the current A1 value for the 2nd save...

Sub writefirst()

    Dim wkb As Workbook
    Set wkb = ThisWorkbook

 wkb.Activate

    ThisWorkbook.Sheets("Round 1").Range("B3").Value = "1"
    Sheets(Array("Quest Info", "Quest Targets", "Milestone Steps", "Quest Config")). _
        Copy
    ActiveWorkbook.SaveAs FileName:= _
        "/Users/me/PuzzleOct2018_1.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close

 wkb.Activate

    ThisWorkbook.Sheets("Round 1").Range("B3").Value = "2"
    Sheets(Array("Quest Info", "Quest Targets", "Milestone Steps", "Quest Config")). _
        Copy
    ActiveWorkbook.SaveAs FileName:= _
        "/Users/dme/PuzzleOct2018_2.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
Comintern
  • 21,855
  • 5
  • 33
  • 80
Daryle Tumacder
  • 11
  • 1
  • 1
    The issue is the second `wkb.Activate` (and the use of `ThisWorkbook` again in the 2nd iteration). See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Comintern Sep 27 '18 at 23:53
  • i tried removing the 2nd instance of wkb.Activazte and changing to ActiveWorkbook vs ThisWorkbook... still the same issue... both newly created workbooks retain a "2" as the string vs a "1" and "2" – Daryle Tumacder Sep 27 '18 at 23:58
  • `ThisWorkbook` is the workbook that contains the executing VBA project. `ActiveWorkbook` is whatever workbook happens to be currently active. They are not interchangeable. Unqualified `Sheets` implicitly refers to the active workbook; qualify it with a `Workbook` object to disambiguate - either `ThisWorkbook` or `wkb`, depending on where these sheets are. `wkb.Activate` and `ActiveWorkbook` are not needed anywhere, just use your `wkb` variable. `wkb.SaveAs`, `wkb.Close`. – Mathieu Guindon Sep 28 '18 at 00:13

0 Answers0