I am trying to automate a weekly task of multiple copying approx 50 series
of data from one workbook and paste to another workbook (not adjacent rows
or columns). The source workbook file changes each week, but is the same
format. The target workbook is the same each week with multiple different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.

Hi Jack

could you give some more information like:
- is there a pattern with which you can find the data to copy
- how is the range specified where it needs to be copied to
- and so on

I can't make myself a picture with the information you gave so far!

Cheers Carlo

"jack" wrote:

I am trying to automate a weekly task of multiple copying approx 50 series
of data from one workbook and paste to another workbook (not adjacent rows
or columns). The source workbook file changes each week, but is the same
format. The target workbook is the same each week with multiple different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.

Currently the target workbook is a collected summary of each week's data and
is set up as a daily record of each individual's data. Each individual has
a daily input by date (daily date row) . There are approx. 50 columns that
require data input each week and these columns are not in a set order, but
could be identified with a designated name or number to correspond with a
range of information from the originating weekly workbook (which has a new
file name each week, containing week ending date in the file name). My
thinking was to associate the data from the originating weekly workbook by
setting up a template (for each new weekly workbook) with named ranges that
correspond to the named column in target workbook and somehow implement copy
/ paste operation. I'm stuck on how to set this up. I think all the macros
should be in the target workbook and would be a "for /next" type method for
the copy / paste of each of the 50 columns.
Any help / ideas would be appreciated. Perhaps you might know of a web site
with a similar example that I could review / use.

"Carlo" wrote in message
...
Hi Jack

could you give some more information like:
- is there a pattern with which you can find the data to copy
- how is the range specified where it needs to be copied to
- and so on

I can't make myself a picture with the information you gave so far!

Cheers Carlo

"jack" wrote:

I am trying to automate a weekly task of multiple copying approx 50
series
of data from one workbook and paste to another workbook (not adjacent
rows
or columns). The source workbook file changes each week, but is the same
format. The target workbook is the same each week with multiple
different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.

Hi Jack

ok, let me get this straight:

you have one worksheet, let's name it "target"
and one worksheet, let's name it "source"
(is there only one "source" for each week???)

The data in "source" is stored in named ranges.

The data in "target" is in columns which are named
exactly like the ranges in "source"

that's for the facts, now for the routine:
(correct me if I do anything different from what you have planned)

- Open Template "Source"
- Data is entered into "Source"
- "Source" is saved as "Source_WEEKNr"
- Close "Source_WEEKNr"
- Open "Target"
- Find "Source_WEEKNr" via VBA
- Import Ranges into Columns
- Close "Target"

Is that so far correct?

Shouldn't be that big a problem, if you want to do it like
that. But why don't you enter the data directly into "Target"

Give me an answer, and we can work this thing out.

Carlo

"jack" wrote:

Currently the target workbook is a collected summary of each week's data and
is set up as a daily record of each individual's data. Each individual has
a daily input by date (daily date row) . There are approx. 50 columns that
require data input each week and these columns are not in a set order, but
could be identified with a designated name or number to correspond with a
range of information from the originating weekly workbook (which has a new
file name each week, containing week ending date in the file name). My
thinking was to associate the data from the originating weekly workbook by
setting up a template (for each new weekly workbook) with named ranges that
correspond to the named column in target workbook and somehow implement copy
/ paste operation. I'm stuck on how to set this up. I think all the macros
should be in the target workbook and would be a "for /next" type method for
the copy / paste of each of the 50 columns.
Any help / ideas would be appreciated. Perhaps you might know of a web site
with a similar example that I could review / use.

"Carlo" wrote in message
...
Hi Jack

could you give some more information like:
- is there a pattern with which you can find the data to copy
- how is the range specified where it needs to be copied to
- and so on

I can't make myself a picture with the information you gave so far!

Cheers Carlo

"jack" wrote:

I am trying to automate a weekly task of multiple copying approx 50
series
of data from one workbook and paste to another workbook (not adjacent
rows
or columns). The source workbook file changes each week, but is the same
format. The target workbook is the same each week with multiple
different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.

Hi Carlo,
You are correct with the routine as you have summarized it. The reason that
the "source" data doesn't go directly into the "target" is that the "source"
file is in a layout that is required by another department and is forwarded
to that dept. on a weekly basis. Actually, the "source" file is compiled
from data coming in, on a weekly basis, from approx. 50 different
individuals and automating that maybe another task I might be working on
later. Yes, there is only one "source" file each week. The "target" file
has weekday rows (with dates) and each column is headed with a unique name
or number. I haven't modified those columns with range names. Would that be
easier to handle? One thought that I had was to do a lookup in the "target"
for the unique name / number column name and find the week ending date based
on the "source" file name (with the week ending date in the file name).
Then mutiple paste the data from "source" file. What are your thoughts?
I'm still a little lost on how to work it through. I need to make the the
interface as user friendly as possible and minimum user intervention as the
individual that will be using it is somewhat unfamiliar with the overall
workings of Excel. My thought was one "button" in the "target" that would
start a macro asking for the "source" file and go on from there. What I am
attempting to automate is now all done manually and I'm unsure exactly how
long it takes. However, I'm confident that if I can get it to work, it will
save a lot of time. Your help getting me on the right track is greatly
appreciated.
Jack


"Carlo" wrote in message
...
Hi Jack

ok, let me get this straight

you have one worksheet, let's name it "target"
and one worksheet, let's name it "source"
(is there only one "source" for each week???)

The data in "source" is stored in named ranges.

The data in "target" is in columns which are named
exactly like the ranges in "source"

that's for the facts, now for the routine:
(correct me if I do anything different from what you have planned)

- Open Template "Source"
- Data is entered into "Source"
- "Source" is saved as "Source_WEEKNr"
- Close "Source_WEEKNr"
- Open "Target"
- Find "Source_WEEKNr" via VBA
- Import Ranges into Columns
- Close "Target"

Is that so far correct?

Shouldn't be that big a problem, if you want to do it like
that. But why don't you enter the data directly into "Target"

Give me an answer, and we can work this thing out.

Carlo

"jack" wrote:

Currently the target workbook is a collected summary of each week's data
and
is set up as a daily record of each individual's data. Each individual
has
a daily input by date (daily date row) . There are approx. 50 columns
that
require data input each week and these columns are not in a set order,
but
could be identified with a designated name or number to correspond with a
range of information from the originating weekly workbook (which has a
new
file name each week, containing week ending date in the file name). My
thinking was to associate the data from the originating weekly workbook
by
setting up a template (for each new weekly workbook) with named ranges
that
correspond to the named column in target workbook and somehow implement
copy
/ paste operation. I'm stuck on how to set this up. I think all the
macros
should be in the target workbook and would be a "for /next" type method
for
the copy / paste of each of the 50 columns.
Any help / ideas would be appreciated. Perhaps you might know of a web
site
with a similar example that I could review / use.

"Carlo" wrote in message
...
Hi Jack

could you give some more information like:
- is there a pattern with which you can find the data to copy
- how is the range specified where it needs to be copied to
- and so on

I can't make myself a picture with the information you gave so far!

Cheers Carlo

"jack" wrote:

I am trying to automate a weekly task of multiple copying approx 50
series
of data from one workbook and paste to another workbook (not adjacent
rows
or columns). The source workbook file changes each week, but is the
same
format. The target workbook is the same each week with multiple
different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.

Hi Jack

ok, I put together a sub and i found a sub on google which helps me find
named ranges.

What you have to do is following:
- Put both files in the same folder
- make a button in Target on sheet targetsheet (name can vary, but it needs
to be changed in the vba code)
- copy the button-sub below into the click event of the button
- copy the function below beneath the click event
(i found the function by googling, and just tweaked it a little)
- change the name of the workbook in the VBA code to the one you want
- enter the unique names for your columns starting in B1 and proceeding to
the right
- click the button

Sub for the button
-------------------------------------------------------------------------------------------
Dim awb As Workbook
Dim row As Integer
Dim wb As Workbook
Dim rngStr As String

Set awb = ActiveWorkbook
row = awb.Worksheets("targetsheet").Cells(65536, 1).End(xlUp).row + 1
'set date in first column of last row
awb.Worksheets("targetsheet").Cells(row, 1).Value = Format(Now(),
"yyyy-mm-dd")

a = ActiveWorkbook.Path & "\source.xls" 'change the name of the workbook
Set wb = Workbooks.Open(a)

awb.Activate

With awb.Worksheets("targetsheet")
For i = 2 To .Cells(1, 256).End(xlToLeft).Column
rngStr = NamedRangeExists(.Cells(1, i).Value, wb.Name)
If rngStr < "" Then
.Cells(row, i).Value = rngStr
End If
Next i
End With

wb.Close
Set wb = Nothing

End Sub
-------------------------------------------------------------------------------------------
function to copy
-------------------------------------------------------------------------------------------
Function NamedRangeExists(strName As String, _
Optional wbName As String) As String
'Declare variables
Dim rngTest As Range, i As Long

'Set workbook name if not set in function, as default/activebook
If wbName = vbNullString Then wbName = ActiveWorkbook.Name

With Workbooks(wbName)
On Error Resume Next

'Loop through all sheets in workbook. In VBA, you MUST specify
' the worksheet name which the named range is found on. Using
' Named Ranges in worksheet functions DO work across sheets
' without explicit reference.
For i = 1 To .Sheets.Count Step 1

'Try to set our variable as the named range.
Set rngTest = .Sheets(i).Range(strName)

'If there is no error then the name exists.
If Err = 0 Then

'Set the function to TRUE & exit
NamedRangeExists = rngTest
Exit Function
Else
'Clear the error
Err.Clear

End If

Next i

End With

End Function
-------------------------------------------------------------------------------------------

hope that gets you started. otherwise just post again.

Carlo


"jack" wrote:

Hi Carlo,
You are correct with the routine as you have summarized it. The reason that
the "source" data doesn't go directly into the "target" is that the "source"
file is in a layout that is required by another department and is forwarded
to that dept. on a weekly basis. Actually, the "source" file is compiled
from data coming in, on a weekly basis, from approx. 50 different
individuals and automating that maybe another task I might be working on
later. Yes, there is only one "source" file each week. The "target" file
has weekday rows (with dates) and each column is headed with a unique name
or number. I haven't modified those columns with range names. Would that be
easier to handle? One thought that I had was to do a lookup in the "target"
for the unique name / number column name and find the week ending date based
on the "source" file name (with the week ending date in the file name).
Then mutiple paste the data from "source" file. What are your thoughts?
I'm still a little lost on how to work it through. I need to make the the
interface as user friendly as possible and minimum user intervention as the
individual that will be using it is somewhat unfamiliar with the overall
workings of Excel. My thought was one "button" in the "target" that would
start a macro asking for the "source" file and go on from there. What I am
attempting to automate is now all done manually and I'm unsure exactly how
long it takes. However, I'm confident that if I can get it to work, it will
save a lot of time. Your help getting me on the right track is greatly
appreciated.
Jack


"Carlo" wrote in message
...
Hi Jack

ok, let me get this straight

you have one worksheet, let's name it "target"
and one worksheet, let's name it "source"
(is there only one "source" for each week???)

The data in "source" is stored in named ranges.

The data in "target" is in columns which are named
exactly like the ranges in "source"

that's for the facts, now for the routine:
(correct me if I do anything different from what you have planned)

- Open Template "Source"
- Data is entered into "Source"
- "Source" is saved as "Source_WEEKNr"
- Close "Source_WEEKNr"
- Open "Target"
- Find "Source_WEEKNr" via VBA
- Import Ranges into Columns
- Close "Target"

Is that so far correct?

Shouldn't be that big a problem, if you want to do it like
that. But why don't you enter the data directly into "Target"

Give me an answer, and we can work this thing out.

Carlo

"jack" wrote:

Currently the target workbook is a collected summary of each week's data
and
is set up as a daily record of each individual's data. Each individual
has
a daily input by date (daily date row) . There are approx. 50 columns
that
require data input each week and these columns are not in a set order,
but
could be identified with a designated name or number to correspond with a
range of information from the originating weekly workbook (which has a
new
file name each week, containing week ending date in the file name). My
thinking was to associate the data from the originating weekly workbook
by
setting up a template (for each new weekly workbook) with named ranges
that
correspond to the named column in target workbook and somehow implement
copy
/ paste operation. I'm stuck on how to set this up. I think all the
macros
should be in the target workbook and would be a "for /next" type method
for
the copy / paste of each of the 50 columns.
Any help / ideas would be appreciated. Perhaps you might know of a web
site
with a similar example that I could review / use.

"Carlo" wrote in message
...
Hi Jack

could you give some more information like:
- is there a pattern with which you can find the data to copy
- how is the range specified where it needs to be copied to
- and so on

I can't make myself a picture with the information you gave so far!

Cheers Carlo

"jack" wrote:

I am trying to automate a weekly task of multiple copying approx 50
series
of data from one workbook and paste to another workbook (not adjacent
rows
or columns). The source workbook file changes each week, but is the
same
format. The target workbook is the same each week with multiple
different
"paste to" ranges each week.
Any suggestions on how I could implement this automation would be
appreciated.