-1

I'm pretty new to vba. I have managed to make some codes work by copying from this site and editing them into my purpose, but this one is just too hard to crack (have tried to solve this for a couple of days now).

I want to copy data from one file and paste it to another file (master file). However, it can't overwrite the existing data, so it should select the first empty cell in a specific column. Please Help Me!! If possible could you add as much as possible comments so that I can understand and learn to do it for myself in the future.

The data files are call logs and these numbers that are copied are results of an analysis that I execute in vba. So there will be a new data file every day, but the actual data that I copy is always located in the same cells. On the other hand the Master file is always the same, but the range where I paste the data changes.

Example Data file: 25 ("S1" in excel data file) 44 ("S2" in exel data file) 8.6.2013 ("S13" in excel data file) These are all created with a vba code that involves formulas, so they will always be in the same cell. There are some other data cells as well, but I hope to learn from this so that I can include them myself.

Master file on the other hand is the same always. The only thing that varies in the Master file is the cell where I paste the data. The cell is practically always the row bellow the previous insert.

Master file: Cell S1 from data file should be copied to Master File "N3", then the next time to "N4" and then to "N5" and so on... so it should find the first free cell in column N.

Cell S2 from data file should be copied to Master file "H3" and from there downward in the same manner as previous.

Cell S13 from data file should be copied to Master file "A3" and downwards in the same manner as previous.

Edit:

This is what works in the master file to retrieve the entire column B, but it overwrites the whole column. Hence it works well to import work hours from one file, that is always the same. Then again for the analysis data file the problem is that the code above works from the master file and in this case when the data file actually changes every day I have no idea how to change this so that it would retrieve the right file. 

Sub Work_hours()

' copy_paste1 Macro

Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long

Set wsMaster = ThisWorkbook.Sheets("Sheet1")
 NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

Set wbDATA = Workbooks.Open("\\C:\users\heppanetti\Google Drive\heppanetti\outbound\working_hours.xlsx")

 With wbDATA.Sheets("Sheet1")
 Columns("B:B").Select
 Selection.Copy
 Windows("ColdCalling_Stats_template.xlsx").Activate
 Columns("B:B").Select
 ActiveSheet.Paste

 wbDATA.Close False
 End With
 End Sub
Community
  • 1
  • 1
Alex Günsberg
  • 118
  • 1
  • 9
  • 1
    I think this [link](http://stackoverflow.com/a/12498033/1635051) helps you to find the first empty row. But to understand better you can always use the "record macro" tool in Excel and do what you want to be happened programatically, after that you can just stop it and check the generated macro code, this would really help you to find out the structure of excel automation. – Reza Ameri Jun 08 '13 at 10:28
  • Thanks! I've actually tried recording macros the entire day, but the the problem is actually finding the empty cell from the master file. I also manage to write a code that copies the right cell but for some reason it doesn't paste it anywhere... and doesn't even report any errors or such. – Alex Günsberg Jun 08 '13 at 13:57
  • 1
    Did you check the link I said? It would help you find the first empty cell. I don't know how to copy and paste a value, you can get the value of a cell and write it in preferred cell by your own. – Reza Ameri Jun 09 '13 at 07:13

1 Answers1

0

I got the first part of the macro working, but now I should be able to switch between documents. The Sender document name is unknown which poses a problem in activation...

Option Explicit

Sub SendToMaster()
'this macro goes IN the sender workbook
Dim wsSEND As Worksheet, wbMASTER As Workbook
Dim NextRow As Long, LastRow As Long

Set wsSEND = ThisWorkbook.Sheets("Sheet1")
 Range("S2").Select
Selection.Copy

Set wbMASTER = Workbooks.Open("\\c:\users\heppanetti\Google Drive\heppanetti\outbound\ColdCalling_stats_template.xlsx ")
Windows("ColdCalling_stats_template.xlsx").Activate
Range("H1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

 'here the macro should activate the SENDER workbook again.
 'However the name is unknown, thus Windows("fileName.xlsm").Activate does not work
  Set wsSEND = ThisWorkbook.Sheets("Sheet1")
  Range("S1").Select
Selection.Copy


 Windows("ColdCalling_stats_template.xlsx").Activate
 Range("N1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False




 wbMASTER.Close True     'save and close the master

 End Sub
Alex Günsberg
  • 118
  • 1
  • 9