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

 

[sijpie]

Brian, do you want to try and code it yourself? You can find plenty examples for each step.

Steps:

1. Define which row needs to be copied
2. Open the target file
3. Get the next open row in the targetfile (and sheet). Easiest is by going up from the last row
4. Set the range (row & columns) where the data needs to goto and
5. set the values by using the DestionationSheet.Range("Ax:Hx").value = SourceSheet.Range("Ay:Hy").value
6. Close the targetfile with save

The copy/paste construction is quite slow. The above method is very fast.

Let me know if you need more help on this

 

[blandreth]

Unfortunately, I do not know how to code. I can modify code to capture where to grab from and where to paste to etc, but can't actually write code.

I appreciate any help I can get.

Thanks,
Brian

 

[sijpie]

OK in that case I will need more information.

1. How do I reckognise which row to copy (source)? Position, or keyword, or selected, something else?
2. Is it just one row to copy?
3. Which columns are to be copied?
4. In the target file, is there a specific sheet to copy to?
5. Is it always the same target file?

 

[blandreth]

Again - I appreciate your help. Below are the answers to your question:

1) Data in A101:AS101 will be copied. I can name it "copydata"
2) Yes - this row will always be the row copied
3) answered in item 1 above - A101:AS101
4) specific sheet will be called "Cut & Etch" in the target file - copied data will be pasted in columns B:AU in the next open row of the target file
5) Target file will always be the same

Please let me know if you need any further information.

 

[sijpie]

Forgot one thing, What is the name of the target file, and where is it located? in the same directory as the working file?

 

[sijpie]

Option Explicit

'1) Data in A101:AS101 will be copied. I can name it "copydata"
'2) Yes - this row will always be the row copied
'3) answered in item 1 above - A101:AS101
'4) specific sheet will be called "Cut & Etch" in the target file - copied data will be pasted in columns B:AU in the next open row of the target file
'5) Target file will always be the same


Sub Copy2Target()
    Dim lR As Long
    Dim rInp As Range
    Dim wbTarget As Workbook, wsTarget As Worksheet
    Dim sPath As String, sTargetFile As String
    
    sPath = ThisWorkbook.Path   '<<<< Assuming in same directory as source book, else put in path
    sTargetFile = "target.xlsx" '<<<< change as required
    
    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
        .Save
        .Close
    End With
End Sub

 

[blandreth]

Thank you for your help. My macro is stopping at this portion of the code shown below. What am I suppose to put in this part of the code

The working sheet that I am completing is on my c:\documents\my folder
The file I am saving too is on my c:\documents\my folder

Do I need to put these targets in somewhere in this code?

I appreciate your help and feedback.

Set wbTarget = Workbooks.Open(Filename:=sPath & "/" & sTargetFile, ReadOnly:=False)

 

[sijpie]

Ah, sorry. In that line change the "/" to a "\"

 

[sijpie]

Also read through the code in your VBA editor and then look for the lines at the top where the comments start with <<<< .
Check the path and filenames there and modify with the correct ones.