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

blandreth

Board Regular
Joined
Jan 18, 2018
Messages
52
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Board Regular
Joined
Jan 18, 2018
Messages
52
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Board Regular
Joined
Jan 18, 2018
Messages
52
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
Code:
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

Board Regular
Joined
Jan 18, 2018
Messages
52
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
Ah, sorry. In that line change the "/" to a "\"
 
Last edited:

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,599
Messages
5,487,782
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top