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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,101,850
Messages
5,483,299
Members
407,392
Latest member
Mrworldwide

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top