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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1

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


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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145547

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("A2:D5").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 wrote:
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

--
joe

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1

I don't understand what yo are trying to do with the autofill. It looks
like it over-writing the data that was pasted. Are yo ujust trying to
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:D" & 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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145547

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dazoloko via OfficeKB.com" <u43760@uwe wrote in message
news:9dd1423447258@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1

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 wrote:
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:D" & 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

--
joe

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145547

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


dazoloko wrote:
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]
--
joe

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200910/1