-1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
mikeloveta
  • 13
  • 1
  • 5
  • 2
    Record a macro, doing each of the steps. Excel will write the code for you, which gives you a place to start. – Ken White Nov 12 '14 at 21:54
  • Thank you Ken. I have tried that and it does help. Keep running into problems. – mikeloveta Nov 14 '14 at 14:49
  • If you've tried, you should have code you can post that would indicate that effort, and then you can explain the "problems" that you're running into and ask a **specific question** about that problem, which we could help you solve. – Ken White Nov 14 '14 at 15:31
  • I don't know how to record a macro and get a choose file box to open which is one thing i'm trying to do. Also i don't know how to record a macro to get the data from the last line of a file i just opened. I apologize for not knowing those things . – mikeloveta Nov 18 '14 at 14:04
  • Then ask a new question (in a separate post) about how to get a file open dialog. Getting the last line of data will be the same as always, whether the file was already open or you just opened it, and there are literally dozens of previous questions here that provide that information. – Ken White Nov 18 '14 at 14:12

1 Answers1

0

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

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24
  • I've got the first 3 sections to work but can't get the last section to copy the last cells in columns F through L. – mikeloveta Nov 20 '14 at 17:24
  • to get, for example the last cell in column F : `set Range_last_F = sheet_Data.cells( sheet_Data.cells(sheet_Data.rows.count,"F").end(xlup).row,"F")` . . I guess if you know how to get the cell, you can now get its value, and copy it. ("F" can also be replaced by 6 without quotes), so you can loop from column F to L by replacing "f" with an integer or Long Variable going from 6 to 12. – Patrick Lepelletier Nov 20 '14 at 23:34