Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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" wrote: 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" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to Move Several Lines to Master Worksheet
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 "MGC" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to move several cells info to next available row on master sh | Excel Discussion (Misc queries) | |||
Wanting to move single lines to second worksheet | Excel Discussion (Misc queries) | |||
how to move lines in conjunction with graphs | Charts and Charting in Excel | |||
Dates period from master worksheet to another worksheet | Excel Discussion (Misc queries) | |||
Pivot Table VBA code to move a row | Excel Discussion (Misc queries) |