Macro to copy data from one file to another in next open row

[blandreth]

I need some macro help. Please see my explanation below of how I want the macro to work:

Run macro in working file --> copies a row of data --> opens another file from specified location --> copies the row of data to the next open row for designated amount of columns --> saves the file --> closes the file and returns to the working file

The data would be pasted in a row for so many columns and the remaining columns in the same row would have formulas that will calculate data pasted. Not sure if this makes a difference, but wanted to add the information just in case.

Any feedback would be greatly appreciated.

Thanks,
Brian

 

[blandreth]

Thanks for the feedback. I change the "/" to "", however still getting hung up at this line. I might not understand the path requirement. In the section with the <<<<< I put the following:

sPath=ThisWorkbook.Path * I did not change this line *
sTargetFile="C:\documents\my folder\report log.xls"

So, just to clarify, I am opening the report from c:\documents\report.xlms - completing the report - saving the report with a different name - then copying data from the report and pasting in a different work book in the next open row: c:\documents\report log.xls

Is there something I need to change for the sPath?

I appreciate your help.

 

[blandreth]

Also, when I save the report it saves the file in a 2019 reports folder in the same drive - c:\documents\2019 reports"filename"

 

[sijpie]

Ah, it gets confusing here:

What needs to come after sPath is the path (without filename) where the target file is held
so: sPath = "C:\documents\my folder"

sTargetFileName is just the name of the workbook:
sTargetFile = "report log.xls"

But now I see you also want to save it to a different folder. And presumably it will need to overwrite the wiorkbook with the same name there (the previous version you saved). Then we need to change the .Save to .SaveAs

Sub Copy2Target()
    Dim lR As Long
    Dim rInp As Range
    Dim wbTarget As Workbook, wsTarget As Worksheet
    Dim sPath As String, sTargetFile As String, sTargetStoreTo as string
    
    sPath = "C:\documents\my folder"   '<<<< Assuming in same directory as source book, else put in path
    sTargetFile = "report log.xls" '<<<< change as required
    sTargetStoreTo = "c:\documents\2019 reports"
    
    Set rInp = ThisWorkbook.Sheets("CopyData").Range("A101:AAS101")
    
    'Open the target file
    Set wbTarget = Workbooks.Open(Filename:=sPath & "/" & sTargetFile, ReadOnly:=False)
    
    With wbTarget
        With .Sheets("Cut & Etch")
            ' Now find next empty row
            lR = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
            
            'and copy the data
            .Range("B" & lR & ":AT" & lR).Value = rInp.Value
        End With
        DisplayAlerts = False   'disable the 'Do you want to overwrite' dialogbox
        .SaveAs sTargetStoreTo & "\" & sTargetFile
        .Close
        DisplayAlerts = True
    End With
End Sub

 

[blandreth]

Hello,

Thanks for another update. I updated the code per your instruction regarding the sPath & sTargetFile. The macro is getting hung up at this line now: Set rInp = ThisWorkbook.Sheets("CopyData").Range("A101:AAS101")

I verified the range is correct and the name "CopyData" is correct for this range.

Also, for the file I'm pasting data into, it does not change the name after data is pasted and file saved. I only need a save function for the report log.

Here is an example of how I want this process to flow:

Open report --> complete report --> run save macro (already installed - save file with new name, i.e. "190729 Report" in a 2019 report folder and closes the blank report without saving to keep it blank) --> run copy/paste macro --> copies data from report (just saved & currently open)("CopyData" - Range "A101:AS101") --> opens report log --> finds next open row in columns B:AU --> pastes copied data --> saves report log (overwrite current file - does not need new name) --> close report log --> return to report

I appreciate any help you can provide and have provided to this point.

Thanks,
Brian