VBA - Copy from Closed File paste to next availabl

Question

This is my first post. Forgive me if i am doing something wrong here. I will be glad to correct any mistakes. I have found the web site to be very valuable as i am a baby in the field of vba. Please have patience with me.

I am a super rookie in VBA. I am learning as i go but have spent a lot of time on this. I find bits and pieces of information on the web but have trouble in putting them all together. I have learned how to make a vba macro that i can select a file and then run other macros. I am using Excel 2013. I complete a time sheet every week(sometimes more at end of month) of the hours i work and the projects i work on. I also include on that sheet when i am out and a code for the reason. I would like to copy three sections to a summary sheet.

1. Cell D1. This cell always has the date beside it. I would like to copy this to the cell in my first row.
2. Cells F3-L3 are cells where a code is put. I would like to copy this to the second cell in my first row.
3. The next range of cells aret the last cells with data in columns F-L. These vary as we have different numbers of rows for work orders each time but are always in columns F-L. I would like to copy this to a second row below the corresponding cells in the first. For the next file I would like to copy to the next available row in summary.

I would like to copy this data so i can figure vacation days, sick days, etc. I know i'm asking alot but would be extremely grateful for any help.

Answer 1

i'm giving you this as Example, you will still need to modify...

Option Explicit 'forces Programmer to declare variables

Sub Button_To_Copy () 'link this to a button or other action that launchs the sub
Dim Range_to_Copy as Range
Dim Range_Destination as Range

Dim Sheet_Data as worksheet 'sheet from where we pull the data
Dim Sheet_Destination as Worksheet' Summary Sheet

set Sheet_Data = Thisworkbook.Sheets("Sheet1") 'you might have to rename the sheetname accordingly to its name.
set Sheet_Destination = Thisworkbook.sheets("Summary") ' wild guess, correct to your summary sheet name

Set Range_to_Copy = Sheet_Data.Range("D1")
Set Range_Destination = Sheet_Destination.range("A1")

Range_to_Copy.Copy Range_Destination  'this copies from range A to B (basically A.copy B), but i changed variable names to make it easier...

'more code, copies

'you can simplify without variables like this:
'Sheets("Sheet1").Range("D1").Copy Sheets("Summary).Range("A1")          <=====    does the same as the above coding

End Sub

Note that i never used activate or select, wich macro recorder will badly use all the time, making bad habits to starters.

Also, by referencing hard cell location like "D1", the code is not dynamic, if you add more data, the sub will have to be changed, so use this just as a start maybe