Code to copy data

[dazoloko via OfficeKB.com]

Hello All

Im wandering if anyone out there can help me.

I have a spreadsheet which contains master data (Persons Details) and on a
separate worksheet a template for a weekly Register.

The master data can vary from week to week as people come and go.

What I do is on a weekly basis is create a register based on the master data
for that week and email it to a colleague to complete.

What I want to be able to do is to automate the process so when I run the
macro it automatically counts the number of rows populated in the master,
selects the first three columns of data, then in the register template,
autimatically insert the number of rows required then paste the data in.

Im fairly new to VBA, and have had a go but just falling short.

Thanks in anticipation.

D

 

[joel]

Where is the register template and where in tthe template do you want t
Insert the data. It may help if you perform the function manually an
recorded a macro. I would recommend doing a copy of the source data an
using the INSERT Copied cells. First use copy for the source data an
then select the 1st location where you want to place the data. Nex
right click mouse and select Insert Copied Cells.


You can post the recorded macro and I can make any required changes t
make it work properly for different number of rows

 

[dazoloko via OfficeKB.com]

Thanks Joe

I Have a a Sheet Called Master Data and Sheet Named Register Template

Ive Recorded the Macro as you said, please see below

Thanks

D

Sub CopyDetails()
'
' CopyDetails Macro
' Macro recorded 19/10/2009 by dchu0506
'

'
Range("A25").Select
Selection.Copy
Sheets("Register Template").Select
Selection.Insert Shift:=xlDown
Range("E7:T7").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("E7:T10"), Type:=xlFillDefault
Range("E7:T10").Select
Range("A1").Select
End Sub

 

[joel]

I don't understand what yo are trying to do with the autofill. It look
like it over-writing the data that was pasted. Are yo ujust trying t
open the same number of rows in the template as the orginal worksheet?


Sub CopyDetails()
'
' CopyDetails Macro
' Macro recorded 19/10/2009 by dchu0506
'

'
With ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2" & LastRow).Copy

End With
With Sheets("Register Template")
.Range("E7").Insert Shift:=xlDown
LastRow = .Range("E" & Rows.Count).End(xlUp).Row
.Range("A1").Select
End With
End Su

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[dazoloko via OfficeKB.com]

Hi Joel

Thanks Joel

Sorry, I missed the bit in the original post woth regard to the autofill.

I am only copying the first 4 columns of data in the master to the register.
On the first row in the register template I have some Data Validation which I
wanted to apply to all lines of the copied data.

I hope that makes sense and Thanks again for the help.

Cheers

D

 

[joel]

The copy methois I used will keep the data validation in the cells

 

[dazoloko via OfficeKB.com]

Thanks for your help Joe much appreciated.

And thanks for your response too Don, Ive managed to get it workin from what
Joe sent through

Cheers

D

Hi Joel

Thanks Joel

Sorry, I missed the bit in the original post woth regard to the autofill.

I am only copying the first 4 columns of data in the master to the register.
On the first row in the register template I have some Data Validation which I
wanted to apply to all lines of the copied data.

I hope that makes sense and Thanks again for the help.

Cheers

D

I don't understand what yo are trying to do with the autofill. It look
like it over-writing the data that was pasted. Are yo ujust trying t

[quoted text clipped - 21 lines]