Copy Paste 1 - 18 Row To A New Worksheet

Microsoft Office excel 2007
August 19, 2010 at 01:39:56
Specs: Windows XP
Hi everyone
i am stuck with this problem, i have 40 lines in my excel can i do a macro that it will copy and paste line 1 to 18 to one worksheet and then 19 to 36 to another worksheet and then 37 to the remaining to another worksheet all within the same workbook. i am thinking of doing a loop that is something like this except my example below is not dynamic nor is a loop

Application.CutCopyMode = False
Sheets.Add After:=Sheets(Sheets.Count)

Sheets.Add After:=Sheets(Sheets.Count)

See More: Copy Paste 1 - 18 Row To A New Worksheet

August 19, 2010 at 05:33:59

I have a few questions:

1. Is this a one-time process or do you intend to repeat running the macro.
2. If it is repeated - will it be re-run on the same workbook
3. If it is re-run on the same workbook, are the same destination worksheets used for each group of rows, or do you want to create three new worksheets each time.
4. If you use the same worksheets, will you overwrite the the original data or will you append it to the original data
5. Is it required that the macro create the three worksheets - if so how will they be named, or will you have 3 worksheets ready to receive the data
6. The naming of the worksheets becomes more important if the macro is to be re-run using the same three destination worksheets - if so please propose the three names - they will have to be hard-coded into the macro.
7. If the number of rows used from 37 onwards sounds as if it can vary. Is there one column that will always contain data on every used row - this column can then be used by the macro to find the last used row.
8. I see in the code you posted that you are cutting the source data. Can you confirm that you want to delete the source data, rather than copy and paste it.


Report •

August 19, 2010 at 17:43:32
Hi Humar
many are my replies

1. I plan to repeat the macro till it sperated all my raw data to different worksheet with the logic that it will generate a new worksheet automatically for every 1-18th row and so on

2. Yes it will be re-run on the same workbook and same worksheet.

3. i hope that the macro can copy and paste to each new worksheet starting from row 6 onwards

4. my original data should be left untouched.

5. I am hoping the macro can help me create the worksheet i need as i won't be able to create them in advance

6. the name you can leave it as default like worksheet 1 2 3 and so on

7. only row 6 onwards is constant..meaning that i always know my first cell is row 6 but my last cell can be anything say 10 or even 100 depending on the raw data at the point of time.

8. sorry i mean copy and paste not cut and paste

Report •

August 20, 2010 at 04:54:02

A follow-up question:
If you create a new worksheet for the data from rows 1 to 18, rows 19 to 36 and rows 37 on, (three worksheets in total) -
where does the new data come from -
You said "it will generate a new worksheet automatically for every 1-18th row"
So, to me this means there is more than one worksheet with source data, or you paste new data into the source worksheet.

Regarding question 7, what I need to know is whether there is one column in the source data that contains values in every row,
For example:

	A	B	C
1	x	y	z
2	x	y	z
3	x		z
4		y	z
5	x	y	z
6	x		z
7	x	y	z
8		y	z
9	x		z
10		y	z
only column C has data on every row.
If I proposed a macro that used column A to find the last row containing data, I would miss one row of data.


Report •

Related Solutions

August 22, 2010 at 23:42:35
i manage to solve it.But thanks for the help =)

Report •

Ask Question