Macro to copy data and print workbook works well, but I need to add data afterwards.

[sands2038]

This code saves me a 5 minutes several times a day.

I fill in a stock acceptence document, export the data to another worksheet that summarises this data, save a copy of the acceptance sheet to a folder, then print the sheet.

I'm using the range E11:H52, although the data is mostly not filling this range it is always within it.

There is an additional step that I need to complete before I've finished this task, I need to add the date from cell D4 into column G, the value from cell D8 into column H, and the week number for the date in D4 into column I, I do this for every row of data that has been copied across.

Hope this makes sense and that someone might be able to help with this.

Sub SaveCopyPrintClose()
     
    Dim newFile As String
    Dim Import As Workbook
    Dim Export As Workbook
    Dim fName As String
   
    Set Import = Workbooks.Open("F:\Desktop\Summary.xlsm")
    Set Export = Workbooks("Stock Acceptance Sheet BLANK.xlsm")
   
    fName = Export.Sheets("Sheet 1").Range("D4").Value
   
    With Export.Sheets("Sheet 1").Range("E11:H52")
        Import.Sheets("Stock In").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize( _
        .Rows.Count, .Columns.Count) = .Value
       
End With
   
    newFile = "Stock Acceptance Sheet - " & fName & ".xlsm"
    ChDir _
    "F:\Desktop\New Folder"
   
    Export.PrintOut
    Export.SaveAs Filename:=newFile
    Export.Close
    Import.Save
    Import.Close
   
End Sub

 

[spiralrain]

As long as vba recognizes the date in cell D4 as a proper date, then I think this may work for you.

Sub SaveCopyPrintClose()

Dim newFile As String
Dim Import As Workbook
Dim Export As Workbook
Dim fName As String
Dim LoopNum As Integer
Dim WeekOfDate As Date

Set Import = Workbooks.Open("F:\Desktop\Summary.xlsm")
Set Export = Workbooks("Stock Acceptance Sheet BLANK.xlsm")

fName = Export.Sheets("Sheet 1").Range("D4").Value

For LoopNum = 11 To 52
On Error GoTo NoData
Import.Sheets("Sheet 1").Range("G" & LoopNum) = Import.Sheets("Sheet 1").Range("D4")
Import.Sheets("Sheet 1").Range("H" & LoopNum) = Import.Sheets("Sheet 1").Range("D8")
WeekOfDate = Import.Sheets("Sheet 1").Range("D4")
Import.Sheets("Sheet 1").Range("I" & LoopNum) = Application.WorksheetFunction.WeekNum(DateSerial(Year(WeekOfDate), Month(WeekOfDate), Day(WeekOfDate)))
NoData:
Next LoopNum

With Export.Sheets("Sheet 1").Range("E11:H52")
Import.Sheets("Stock In").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize( _
.Rows.Count, .Columns.Count) = .Value

End With

newFile = "Stock Acceptance Sheet - " & fName & ".xlsm"
ChDir _
"F:\Desktop\New Folder"

Export.PrintOut
Export.SaveAs Filename:=newFile
Export.Close
Import.Save
Import.Close

End Sub

 

[sands2038]

Closer than ever.

This new code seems to put the data into rows 11 - 52 everytime, regardless of how many rows I've copied across. It is generating the right data though.
The original code adds data to the bottom of an ever expanding list, and it could be 3 lines one day and 25 the next.

Does that make sense?

 

[spiralrain]

That's odd. I didn't change the code that was there, just added a section to put in the dates where you wanted them on the originating sheet (Import). What change from the current code are you looking to make? If you want the data in the export file to appear in a different place, all you should need to do is replace:

With Export.Sheets("Sheet 1").Range("E11:H52")

with the place you want to add the information such as:

With Export.Sheets("Sheet 1").Range("E1:H42")

As far as adding data to the bottom of an ever expanding list, that should be doable, but more details are needed, such as which file you're talking about. Looking at the original code in the first post, I'm not seeing anything looks like information is being added to an existing list, but I very well could be misreading it.