J
JackGombola
I have used the Data Validation feature of Excel 2003 to survey the members
of my bowling league. Data Validation allowed the teams to respond to my
questions directly into the spreadsheet I sent out to them. I have been able
to combine all of the separate spreadsheets I've received from league members
into one master spreadsheet containing five rows of data representing each
respondent. I found macro code on this site to combine all of the
spreadsheets into one master sheet.
What I need now is macro code that will loop through this master spreadsheet
to reformat this data by selectively copying various ranges within it to
another spreadsheet in the same workbook. For example:
1. Copy "Values" only from cell A1 in sheet1 to cells B3 thru B7 in sheet2,
then delete the copied data from sheet1.
2. Copy "Values" only from cells B3 thru B7 in sheet1 to cells D8 thru D12
in sheet2, then delete the copied data from sheet1. I’m deleting so that I
can verified that all the data has been copied to the reformatted sheet
(sheet2).
I have a number of these Copy/Paste Special (Values) only operations to
perform until the end of my master is reached or 500 rows have been
processed; whatever is easier to code. I'm guessing some kind of loop is
needed with variables used to increment the changing source and destination
row and column addresses of my sheet1 and sheet data ranges. I.E. When the
data in the second set of 5 rows is reformatted it needs to be copied just as
items #1 and #2 shown above but the row numbers would need to be advanced by
5 since each respondent's data consists of 5 rows.
Any and all help is appreciated.
Thanks,
John
of my bowling league. Data Validation allowed the teams to respond to my
questions directly into the spreadsheet I sent out to them. I have been able
to combine all of the separate spreadsheets I've received from league members
into one master spreadsheet containing five rows of data representing each
respondent. I found macro code on this site to combine all of the
spreadsheets into one master sheet.
What I need now is macro code that will loop through this master spreadsheet
to reformat this data by selectively copying various ranges within it to
another spreadsheet in the same workbook. For example:
1. Copy "Values" only from cell A1 in sheet1 to cells B3 thru B7 in sheet2,
then delete the copied data from sheet1.
2. Copy "Values" only from cells B3 thru B7 in sheet1 to cells D8 thru D12
in sheet2, then delete the copied data from sheet1. I’m deleting so that I
can verified that all the data has been copied to the reformatted sheet
(sheet2).
I have a number of these Copy/Paste Special (Values) only operations to
perform until the end of my master is reached or 500 rows have been
processed; whatever is easier to code. I'm guessing some kind of loop is
needed with variables used to increment the changing source and destination
row and column addresses of my sheet1 and sheet data ranges. I.E. When the
data in the second set of 5 rows is reformatted it needs to be copied just as
items #1 and #2 shown above but the row numbers would need to be advanced by
5 since each respondent's data consists of 5 rows.
Any and all help is appreciated.
Thanks,
John