Excel – Macro Problem – Appending Data

[Aussiexcel]

Gday Mates.

I need help with appending data from one worksheet to another in the
same workbook. I will list all the details now.

Workbook name Test.xls
Worksheets name in order of tabs D1,D2,D3,D4,D5,Master
D1 to D5 are in the same format.

What the macro has to do in order
1. Clear the contents of the Master worksheet from ‘A5’ till ‘S5’ down
till the end of file (or end of text).

2. Now that the cells are cleared in master I want to start coping from
D1 into Master Worksheet

3. The Macro needs to Copy column ‘A11’ from D1 into Master column ‘A5’

The Macro needs to Copy column ‘B11’ from D1 into Master column ‘B5’

The macro needs to copy from column ‘A11’ till ‘A??’ is empty and paste
in ‘A5’. Column B is the same. Also I need to store how many cells I
pasted so that the cells don’t mixed together and corrupt my
worksheet.

4. After that the macro needs to Copy column C11 from D1 into Master
column H5
After that the macro needs to Copy column D11 from D1 into Master
column I5

The macro has to copy from C11 to the same length as the A11 copy was
so that the data is not mixed together and pasted in H5. Column D is
the same.

5. Then the macro needs to Copy column E11 from D1 into Master column
G5.
The macro has to copy from E11 to the same length as the A11 copy was
so that the data is not mixed together and pasted in G5.

6. After that the macro needs to Copy cell C1 from D1 into Master
column D5
After that the macro needs to Copy cell B1 from D1 into Master column
E5

The macro has to copy from cell C1 from D1 and paste down the Master
column D5 the same length as the A11 so that the data can be identified
column from that D1. Same for B2.

Cell C1 has a unique number for the D1 sheet which needs to be placed
in Master Column so that I can identify where data came from in the
Master.

B2 has the comment for the unique number for it.

7. Now I have done all that. I need to do the copy the same information
from D2,D3,D4,D5 into the Master but appending it so it pastes the
information from D2 at the end of D1. Then D3 pastes at the end of D2.
Then D4 pastes at the end of D3. Then D5 pastes at the end of D4.
8. Save all information.

Now can anyone help me with this or is it not possible to do? I have
been trying for a day now but I am finding it hard. I will be checking
the forum a lot to see if any of you gurus can help me?

I have looked at most of the posts to do with appending and one was
helpful but I am having problems with the step1 (I delete everything on
the sheet and how do u know a cell is empty), step 3 (I can copy a whole
sheet but I only want the columns), step 6 (cant copy one cell into many
down the column) and step 7 is the hardest part appending only the
columns.

I know it’s a lot to ask but I will take any help. I have only used
programmed in macros once before and that was 2 years ago.

Does it make sense what I am doing? If you need more info reply and I
will happily tell you all I know.

Cheers,

Aussie

 

[Don Guillett]

You did not post your coding efforts for comments. It appears you desire a
project instead of an answer to a question.

 

[Tom Ogilvy]

with Worksheets("Master")
.Range("A5:S" & .Rows.count).ClearContents
End With

Will do the clearing you describe.

Dim rng as Range
with Worksheets("D1")
set rng = .Cells(.rows.count,1).End(xlup)
End With

will find the last filled cell in Column A of worksheet D1. You can adapt
this to do similar work in any sheet and/or column.

rng.row will tell you what row that is.

 

[Aussiexcel]

thanks for ur advice!

its done no