0

I have an interesting issue where I am trying to have a looprange in effect and before looping to the next data point it will save a copy of the worksheet. Does anyone have any experience, when I try to make the changes it causes issues with my Next statement at the bottom of the code.

''' 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
wb.SaveAs ThisWorkbook.Path & "\" & Range("C12")
ws.Copy before = wb.Worksheets(1)

Next rngLoopRange

Set wsSummary = Nothing

MsgBox "Complete!", vbInformation

End Sub '''

Jack Staniforth
  • 13
  • 3
  • "Issues"? Do you get an error message? What is it? Could you include the beginning of the loop as well? You may also want to include the exact values of your variables/range when it errors. – braX Jan 27 '20 at 11:47
  • I'm really just trying to get a replacement code for the pdf export and replace it with a save worksheet as new workbook without causing issues with the Next code. – Jack Staniforth Jan 27 '20 at 11:51
  • Well then you should probably explain what the issues are... – braX Jan 27 '20 at 11:52
  • Just getting a 'Next without For' error when I replace it with some save code – Jack Staniforth Jan 27 '20 at 11:58
  • Yes, well that is kinda my point... you dont have a matching `For` statement in your question, so if that's all the code you are using, it's not going to work. – braX Jan 27 '20 at 15:55
  • '''' 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 ThisWorkbook.Sheets("Sheet1").Copy Before:=wb.Sheets(1) wb.SaveAs Range("I11") Next rngLoopRange Set wsSummary = Nothing MsgBox "Complete!", vbInformation End Sub ''' – Jack Staniforth Jan 28 '20 at 09:54
  • Sorry, attached is the full code that won't format properly, I'm attempting to run through the loop and at each instance of the loop it saves a new workbook with the information on. – Jack Staniforth Jan 28 '20 at 09:55
  • Please post your code in your question by editing it. – braX Jan 28 '20 at 09:55

0 Answers0