Code to Move Several Lines to Master Worksheet

[MGC]

I am working with 30+ workbooks which contain timesheets (tabs) for every day
a job is worked in our plant. I have inserted rows at the bottom of the
timesheets which will gather the information from the timesheet above so that
this information can then be moved to the first blank row in a master sheet
in another workbook. I'm thinking there has to be an easier way than copying
and pasting the data each time. The number of lines to be moved will vary
from day to day. If anyone can help, it would be much appreciated!

 

[Joel]

You need VBA macro code. Lots of people have the same request. I need more
details to help. You need tell me more information.

1) Are all the excel files in one directory or organized some other way?
2) Do you want get data from all workbooks in the directory or files with
cetain names?
3) What is the name of the worksheeet(s) you want to copy data from?
4) Do you want entire rows copied orjust some columns
5) What is the name of the detination worksheet(s) in the master workbook.
6) How do we identify the last row(s) of data that needs to be copied. Your
request says that you copy multiple rows. How many rows.

 

[MGC]

Joel:

1.) All of the excel files are in the same folder. However, this folder
will be moved several times before it reaches it's final destination

2.) The information to be extracted will reside in different workbooks and
on tabs which will have different names

3.) The worksheets will be named differently depending upon the job...there
is no way I can change the names as the forms being used are used by many
people within our organization.

4.) I would like to copy entire rows (no columnar data)

5.) The name of the destination sheet is EntryMaster

6.) The number of rows to be copied will vary, but no more than
9...actually A60 to W68. The number copied will be dependent upon the data
within them If the cells which they are linked to were not used that day
(let's say 4 people didn't work), then only five ines would need to be
carried onto the EntryMaster tab (by the way, this resides in a workbook
named Timesheet Template.)

Hope this helps. If you need more info, please let me know. Thanks for
your help!

 

[Joel]

We still need to clarify a few points.

1) Is every file in the folder going to get opened or only files with
certain names? You mentioned Timesheet Template. Do all the files that get
opened have "Timesheet" as part of the name? I could open only these
worksheets.

2) You said rows 60-68 needed to be copied, but then in some cases only some
of the rows. Are the rows that don't need to be copied blank, or is there
some other indication when to copy and when not to copy?

3) Does the macro get run on all worksheets in a workbook? I can check the
names of each worksheet and only copy that data from specific worksheets.
Need more detail.

You will need to test the code in a test directory before trying it on real
files. but the code shouldn't cause and problems becausue we are only
copying data to the master workbook. The only problems will be getting too
little data or too much data.

 

[MGC]

Joel:

I can see this macro has the potential to be more complicated than I
originally thought. Because we run the risk of gathering too much or too
little data from the worksheets due to there being no foolproof way for Excel
to know whether the cells contain 'true' data, I believe I will have the user
utilize copy and paste for now. Thank you for your help the last couple of
posts!

 

[Joel]

I strongly recommend you do automate your process. It will save a lot of
time. Below is code I wrote for another posting similar to your request.
The most important thing to remember when you want to extract data is
organize the date properly so it can be easily extracted.

Here are some ways to organize data
1) Put files you want to extract data all in one directory
2) Name files using a convention. example "Time Sheet Joel.xls", "Time
Sheet Mary.xls". You can search for all file names starting with Time Sheet
3) In a worksheet, have a column hours. You may want to copy only hours = 0
indicating which people didn't work on a particular day.

Sub gettotals()

Const Clientfolder = "c:\temp\test"

RowCount = 1
first = True
Do
If first = True Then
Filename = Dir(Clientfolder & "\*.xls")
first = False
Else
Filename = Dir()
End If

If Filename <> "" Then
Workbooks.Open (Clientfolder & "\" & Filename)
Worksheets("Master").Activate

Range("U3").Copy Destination:= _
ThisWorkbook.ActiveSheet.Range("A" & RowCount)

Range("W3").Copy Destination:= _
ThisWorkbook.ActiveSheet.Range("B" & RowCount)

Workbooks(Filename).Close

RowCount = RowCount + 1
End If

Loop While Filename <> ""

End Sub