0

I have an issue runtime error 1004 with the below code, could anyone clarify for me what could be driving this?

Sub Excel1()

   Dim rngLoopRange As Range
   Dim wsSummary As Worksheet
   Dim rngDealers As Worksheet

   Set wsSummary = Sheets("PL")

   For Each rngLoopRange In Worksheets("AUX").Range("A1:A38")
      wsSummary.Range("C12").Value = rngLoopRange.Value

    Application.Run "TM1REFRESH"

    Dim wb As Workbook
    Set wb = Workbooks.Add
    ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\" & Range("C12").Value
    ws.Copy before = wb.Worksheets(1)


Next rngLoopRange

Set wsSummary = Nothing

    MsgBox "Complete!", vbInformation

End Sub

The above is now saving the file using the name I wanted, can you please advise now why it is giving me an

error '424'

on the sheet copying over. with the code line ws.Copy before = wb.Worksheets(1)

Sub Excel1()

   Dim rngLoopRange As Range
   Dim wsSummary As Worksheet
   Dim rngDealers As Worksheet

   Set wsSummary = Sheets("PL")

   For Each rngLoopRange In Worksheets("AUX").Range("A1:A38")
      wsSummary.Range("C12").Value = rngLoopRange.Value

    Application.Run "TM1REFRESH"

    Dim wb As Workbook
    Set wb = Workbooks.Add
    ActiveWorkbook.SaveAs filename:=ThisWorkbook.Path & "\" & wsSummary.Range("C12").Value
    ws.Copy before:=wb.Worksheets(1)
    wb.Close savechanges:=True


Next rngLoopRange

Set wsSummary = Nothing

    MsgBox "Complete!", vbInformation

End Sub
TylerH
  • 20,799
  • 66
  • 75
  • 101
Jack Staniforth
  • 13
  • 3
  • Which line generates an error, and are you sure that `Range("C12").Value` is not empty? – Vitaliy Prushak Jan 28 '20 at 10:46
  • Line C12 definitely has a value, but it is this line causing the problem, it is a formula though, would that be an issue? – Jack Staniforth Jan 28 '20 at 10:49
  • You just added a new workbook, which will be active and C12 on its active sheet will be blank. Specify the sheet and workbook for the range too. – Rory Jan 28 '20 at 10:53
  • But I'm copying the data onto the new workbook, surely that populates the cell and then it looks at it that cell for reference to save? – Jack Staniforth Jan 28 '20 at 10:55
  • No, you have a value C12 on Worksheets("AUX"), then you create a blank workbook and trying to take data of new book's C12, which is empty. This is how your code written. – Vitaliy Prushak Jan 28 '20 at 11:00
  • Can you please advise how I correct this? – Jack Staniforth Jan 28 '20 at 11:08
  • 1
    Think you need to add a reference to the sheet C12 is on - `wsSummary.Range("C12").Value` similarly you might want to consider if `ActiveWorkbook` needs to be more specific eg. `wb` – maxhob17 Jan 28 '20 at 11:17

0 Answers0