Cant get it to write to the file

timk5000

New Member
Joined
Feb 29, 2016
Messages
8
I am trying to create new workbooks for every row in a sheet.

I have been able to do this before but I am missing something this time.
It seems every section of code below works except 1.

It seems to work correctly until I get to the section that writes the values to the template.
It will even create the new files and save them with the correct file names.
But they are empty

I am pretty sure it is something in the bolded section.
I know I should be declaring variable but I believe something else is wrong also.

Thanks

Rich (BB code):
Sub Create_Requests()

'Get Number of Rows in PartsList Sheet

With Sheets("PartsList")
  NbrOfParts = Application.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
  MsgBox NbrOfParts
  End With
Application.ScreenUpdating = False

'Process Each Row in PartsList

For I = 2 To NbrOfParts

doit = ActiveWorkbook.Worksheets("PartsList").Cells(I, 5).Value


If doit = 1 Then 'Only Process if this is True

'Assign values to variables

With Sheets("PartsList")
PartFor = Cells(I, "A").Value
RequestedBy = Cells(I, "B").Value
RequestDate = Cells(I, "C").Value
SubSystem = Cells(I, "D").Value
PartNumber = Cells(I, "F").Value
Description = Cells(I, "G").Value
Cost = Format(Cells(I, "H").Value, "##,##0.00")
MonthlyUsage = Cells(I, "J").Value
MinimumStock = Cells(I, "K").Value
MaximumStock = Cells(I, "L").Value
ReorderPoint = Cells(I, "M").Value
ReorderQty = Cells(I, "N").Value
Vendor = Cells(I, "P").Value
End With

'Write variables values to Template

   With Sheets("Template")
    Cells(6, "B").Value = Description
    Cells(8, "B").Value = Vendor
    Cells(10, "B").Value = PartNumber
    Cells(12, "B").Value = Cost
    Cells(23, "D").Value = PartFor '& "- (" & SubSystem & ")"
    Cells(25, "C").Value = MonthlyUsage
    Cells(27, "C").Value = MinimumStock
    Cells(29, "C").Value = MaximumStock
    Cells(31, "C").Value = ReorderPoint
    Cells(33, "C").Value = ReorderQty
    Cells(36, "C").Value = RequestedBy
    Cells(36, "H").Value = RequestDate
  End With
          
   'Copy Template range and save it to new Workbook
  
   ActiveWorkbook.Worksheets("Template").Range("A1:H43").Copy
   
    Name = PartFor & " " & I
    Dim WB As Workbook 'creates a reference to workbook object
    Set WB = Workbooks.Add
       
  ActiveWorkbook.Worksheets("Sheet1").Range("A1:H43").PasteSpecial xlPasteColumnWidths
  ActiveWorkbook.Worksheets("Sheet1").Range("A1:H43").PasteSpecial xlPasteAll
 
  ActiveWorkbook.SaveAs Filename:="X:\Inventory Requests\" & PartFor & "\" & Name & ".xlsx" 'saves at the given file location and name

  ActiveWorkbook.Close
 
  End If
       
Next I ' Get next Part from list
Application.ScreenUpdating = True

End Sub
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You are correct. You are using a With statement which means that all child elements must be preceded with a period (dot).
VBA Code:
 With Sheets("Template")
    .Cells(6, "B").Value = "Description"
    .Cells(8, "B").Value = "Vendor"
    .Cells(10, "B").Value = "PartNumber"
    .Cells(12, "B").Value = "Cost"
    .Cells(23, "D").Value = "PartFor "& "- (" & SubSystem & ")"
    .Cells(25, "C").Value = "MonthlyUsage"
    .Cells(27, "C").Value = "MinimumStock"
    .Cells(29, "C").Value = "MaximumStock"
    .Cells(31, "C").Value = "ReorderPoint"
    .Cells(33, "C").Value = "ReorderQty"
    .Cells(36, "C").Value = "RequestedBy"
    .Cells(36, "H").Value = "RequestDate"
  End With

Otherwise it defaults to the active sheet. Also put quote marks on your headers.
 
Last edited:
Solution

Forum statistics

Threads
1,136,909
Messages
5,678,513
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top