Macro to update with active(live) workbooks

I am trying to record a macro that will pull from several different
workbooks with active data. For Example: I want to take the data in
workbook1 column "A" and copy and paste it to column "A" in workbook3, then
look at workbook2 and copy and paste the data in column "A" to the first
open cell in workbook3 column "A". It then needs to do the same thing for
column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" & "C" will
be changing daily. So workbook1 column "A", "B" & "C" will look like the
following.......

Monday...

Workbook1 Workbook2
Workbook3
A B C A B C A B C
1 4 7 10 1 16 1 4 7
2 5 8 11 14 17 2 5 8
3 6 9 12 15 18 3 6 9
10 1 16
11 14 17
12 15 18



Tuesday... after we have made updates the new items are indicated in red

Workbook1 Workbook2
Workbook3

A B C A B C A B C
1 4 7 10 1 16 1 4 7
2 5 8 11 14 17 2 5 8
3 6 9 12 15 18 3 6 9
1 3 5 7 9 11 1 3 5
2 4 6 8 10 12 2 4 6
10 1 16
11 14 17
12 15 18
7 9 11
8 10 12


Wednesday etc......

The problem I run into when I try to record this macro is that I need for it
to look in the entire column "A" for any additions and copy all data and
paste it into Column "A" then it needs to look in the entire column "B" and
paste in column "B" where the last piece of data in column "B" from
workbook1 stopped. When I have tried to record in the past it will copy all
of column "A" in workbook1 but when it copies all of "A" in workbook2 it
only recognizes where I told it to start previously so it essentially copies
over all of the "new" data from column "A" in workbook1.

Any help or guidance would be appreciated.


Thanks

Judd

As usual, you should post your code for comments but
x=cells(rows.count,"a").end(xlup).row
will give you the last row in the column desired so
..copy cells(x,1) will copy to the last row in col A



--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> I am trying to record a macro that will pull from several different
> workbooks with active data. For Example: I want to take the data in
> workbook1 column "A" and copy and paste it to column "A" in workbook3,
then
> look at workbook2 and copy and paste the data in column "A" to the first
> open cell in workbook3 column "A". It then needs to do the same thing for
> column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" & "C"
will
> be changing daily. So workbook1 column "A", "B" & "C" will look like the
> following.......
>
> Monday...
>
> Workbook1 Workbook2
> Workbook3
> A B C A B C A B C
> 1 4 7 10 1 16 1 4 7
> 2 5 8 11 14 17 2 5 8
> 3 6 9 12 15 18 3 6 9
> 10 1 16
> 11 14 17
> 12 15 18
>
>
>
> Tuesday... after we have made updates the new items are indicated in red
>
> Workbook1 Workbook2
> Workbook3
>
> A B C A B C A B C
> 1 4 7 10 1 16 1 4 7
> 2 5 8 11 14 17 2 5 8
> 3 6 9 12 15 18 3 6 9
> 1 3 5 7 9 11 1 3 5
> 2 4 6 8 10 12 2 4 6
> 10 1 16
> 11 14 17
> 12 15 18
> 7 9 11
> 8 10 12
>
>
> Wednesday etc......
>
> The problem I run into when I try to record this macro is that I need for
it
> to look in the entire column "A" for any additions and copy all data and
> paste it into Column "A" then it needs to look in the entire column "B"
and
> paste in column "B" where the last piece of data in column "B" from
> workbook1 stopped. When I have tried to record in the past it will copy
all
> of column "A" in workbook1 but when it copies all of "A" in workbook2 it
> only recognizes where I told it to start previously so it essentially
copies
> over all of the "new" data from column "A" in workbook1.
>
> Any help or guidance would be appreciated.
>
>
> Thanks
>
> Judd
>
>

Don,
Ok... This is what I have come up with.




I need this to look at column "A" Column "B" etc... and copy column "A" just
the data that is in column "A" and paste to Column "C" and do the same for
column "B" . This data will be added and subtracted to daily so it must
know where the last item in column "A" is and add it to column "C" on the
new workbook then copy the data in column "B" and add to the new workbook
where the data from column "A" left off.



Sub Macro1()

'If each column has a heading, change the 1's to 2's

Dim myRange As Range

Set myRange = Range("A1", Range("A1").End(xlDown))

Workbooks.Add

Sheets("Sheet1").Activate

myRange.Copy Range("C1")

Set myRange = Range("B1", Range("B1").End(xlDown))

myRange.Copy Range("C65536").End(xlUp).Offset(1)

End Sub



This so far is doing what I need it to do by copying column "A" and opening
a new workbook and adding to colum "C" in this new workbook, and I think it
is trying to copy "B" but I keep receiving the following message "Run-time
error 1004 the information cannot be pasted because the copy and paste area
are not the same size and shape"





Any Assistance would be greatly appreciated.



Judd



"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> As usual, you should post your code for comments but
> x=cells(rows.count,"a").end(xlup).row
> will give you the last row in the column desired so
> .copy cells(x,1) will copy to the last row in col A
>
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
>> I am trying to record a macro that will pull from several different
>> workbooks with active data. For Example: I want to take the data in
>> workbook1 column "A" and copy and paste it to column "A" in workbook3,
> then
>> look at workbook2 and copy and paste the data in column "A" to the first
>> open cell in workbook3 column "A". It then needs to do the same thing for
>> column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" & "C"
> will
>> be changing daily. So workbook1 column "A", "B" & "C" will look like the
>> following.......
>>
>> Monday...
>>
>> Workbook1 Workbook2
>> Workbook3
>> A B C A B C A B C
>> 1 4 7 10 1 16 1 4 7
>> 2 5 8 11 14 17 2 5 8
>> 3 6 9 12 15 18 3 6 9
>> 10 1 16
>> 11 14 17
>> 12 15 18
>>
>>
>>
>> Tuesday... after we have made updates the new items are indicated in red
>>
>> Workbook1 Workbook2
>> Workbook3
>>
>> A B C A B C A B C
>> 1 4 7 10 1 16 1 4 7
>> 2 5 8 11 14 17 2 5 8
>> 3 6 9 12 15 18 3 6 9
>> 1 3 5 7 9 11 1 3 5
>> 2 4 6 8 10 12 2 4 6
>> 10 1 16
>> 11 14 17
>> 12 15 18
>> 7 9 11
>> 8 10 12
>>
>>
>> Wednesday etc......
>>
>> The problem I run into when I try to record this macro is that I need for
> it
>> to look in the entire column "A" for any additions and copy all data and
>> paste it into Column "A" then it needs to look in the entire column "B"
> and
>> paste in column "B" where the last piece of data in column "B" from
>> workbook1 stopped. When I have tried to record in the past it will copy
> all
>> of column "A" in workbook1 but when it copies all of "A" in workbook2 it
>> only recognizes where I told it to start previously so it essentially
> copies
>> over all of the "new" data from column "A" in workbook1.
>>
>> Any help or guidance would be appreciated.
>>
>>
>> Thanks
>>
>> Judd
>>
>>
>
>

try it this way
Sub Macro1()
'If each column has a heading, change the 1's to 2's
Dim myRange As Range
Set myRange = Range("A1", Range("A1").End(xlDown))
Workbooks.Add
'Sheets("Sheet1").Activate
myRange.Copy Range("C1")
'Set myRange = Range("B1", Range("B1").End(xlDown))
'myRange.Copy Range("C65536").End(xlUp).Offset(1)
End Sub


--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:%[email protected]...
> Don,
> Ok... This is what I have come up with.
>
>
>
>
> I need this to look at column "A" Column "B" etc... and copy column "A"
just
> the data that is in column "A" and paste to Column "C" and do the same for
> column "B" . This data will be added and subtracted to daily so it must
> know where the last item in column "A" is and add it to column "C" on the
> new workbook then copy the data in column "B" and add to the new workbook
> where the data from column "A" left off.
>
>
>
> Sub Macro1()
>
> 'If each column has a heading, change the 1's to 2's
>
> Dim myRange As Range
>
> Set myRange = Range("A1", Range("A1").End(xlDown))
>
> Workbooks.Add
>
> Sheets("Sheet1").Activate
>
> myRange.Copy Range("C1")
>
> Set myRange = Range("B1", Range("B1").End(xlDown))
>
> myRange.Copy Range("C65536").End(xlUp).Offset(1)
>
> End Sub
>
>
>
> This so far is doing what I need it to do by copying column "A" and
opening
> a new workbook and adding to colum "C" in this new workbook, and I think
it
> is trying to copy "B" but I keep receiving the following message "Run-time
> error 1004 the information cannot be pasted because the copy and paste
area
> are not the same size and shape"
>
>
>
>
>
> Any Assistance would be greatly appreciated.
>
>
>
> Judd
>
>
>
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > As usual, you should post your code for comments but
> > x=cells(rows.count,"a").end(xlup).row
> > will give you the last row in the column desired so
> > .copy cells(x,1) will copy to the last row in col A
> >
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> I am trying to record a macro that will pull from several different
> >> workbooks with active data. For Example: I want to take the data in
> >> workbook1 column "A" and copy and paste it to column "A" in workbook3,
> > then
> >> look at workbook2 and copy and paste the data in column "A" to the
first
> >> open cell in workbook3 column "A". It then needs to do the same thing
for
> >> column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" & "C"
> > will
> >> be changing daily. So workbook1 column "A", "B" & "C" will look like
the
> >> following.......
> >>
> >> Monday...
> >>
> >> Workbook1 Workbook2
> >> Workbook3
> >> A B C A B C A B C
> >> 1 4 7 10 1 16 1 4 7
> >> 2 5 8 11 14 17 2 5 8
> >> 3 6 9 12 15 18 3 6 9
> >> 10 1 16
> >> 11 14 17
> >> 12 15 18
> >>
> >>
> >>
> >> Tuesday... after we have made updates the new items are indicated in
red
> >>
> >> Workbook1 Workbook2
> >> Workbook3
> >>
> >> A B C A B C A B C
> >> 1 4 7 10 1 16 1 4 7
> >> 2 5 8 11 14 17 2 5 8
> >> 3 6 9 12 15 18 3 6 9
> >> 1 3 5 7 9 11 1 3 5
> >> 2 4 6 8 10 12 2 4 6
> >> 10 1 16
> >> 11 14 17
> >> 12 15 18
> >> 7 9 11
> >> 8 10 12
> >>
> >>
> >> Wednesday etc......
> >>
> >> The problem I run into when I try to record this macro is that I need
for
> > it
> >> to look in the entire column "A" for any additions and copy all data
and
> >> paste it into Column "A" then it needs to look in the entire column "B"
> > and
> >> paste in column "B" where the last piece of data in column "B" from
> >> workbook1 stopped. When I have tried to record in the past it will copy
> > all
> >> of column "A" in workbook1 but when it copies all of "A" in workbook2
it
> >> only recognizes where I told it to start previously so it essentially
> > copies
> >> over all of the "new" data from column "A" in workbook1.
> >>
> >> Any help or guidance would be appreciated.
> >>
> >>
> >> Thanks
> >>
> >> Judd
> >>
> >>
> >
> >
>
>

This code works however it does not copy and paste column "B" in the
existing workbook into column "C" to the new workbook

Sub Macro1()
'If each column has a heading, change the 1's to 2's
Dim myRange As Range
Set myRange = Range("A1", Range("A1").End(xlDown))
Workbooks.Add
'Sheets("Sheet1").Activate
myRange.Copy Range("C1")
'Set myRange = Range("B1", Range("B1").End(xlDown))
'myRange.Copy Range("C65536").End(xlUp).Offset(1)
End Sub

Any advice is greatly appreciated.

Judd

"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> try it this way
> Sub Macro1()
> 'If each column has a heading, change the 1's to 2's
> Dim myRange As Range
> Set myRange = Range("A1", Range("A1").End(xlDown))
> Workbooks.Add
> 'Sheets("Sheet1").Activate
> myRange.Copy Range("C1")
> 'Set myRange = Range("B1", Range("B1").End(xlDown))
> 'myRange.Copy Range("C65536").End(xlUp).Offset(1)
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:%[email protected]...
>> Don,
>> Ok... This is what I have come up with.
>>
>>
>>
>>
>> I need this to look at column "A" Column "B" etc... and copy column "A"
> just
>> the data that is in column "A" and paste to Column "C" and do the same
>> for
>> column "B" . This data will be added and subtracted to daily so it must
>> know where the last item in column "A" is and add it to column "C" on the
>> new workbook then copy the data in column "B" and add to the new workbook
>> where the data from column "A" left off.
>>
>>
>>
>> Sub Macro1()
>>
>> 'If each column has a heading, change the 1's to 2's
>>
>> Dim myRange As Range
>>
>> Set myRange = Range("A1", Range("A1").End(xlDown))
>>
>> Workbooks.Add
>>
>> Sheets("Sheet1").Activate
>>
>> myRange.Copy Range("C1")
>>
>> Set myRange = Range("B1", Range("B1").End(xlDown))
>>
>> myRange.Copy Range("C65536").End(xlUp).Offset(1)
>>
>> End Sub
>>
>>
>>
>> This so far is doing what I need it to do by copying column "A" and
> opening
>> a new workbook and adding to colum "C" in this new workbook, and I think
> it
>> is trying to copy "B" but I keep receiving the following message
>> "Run-time
>> error 1004 the information cannot be pasted because the copy and paste
> area
>> are not the same size and shape"
>>
>>
>>
>>
>>
>> Any Assistance would be greatly appreciated.
>>
>>
>>
>> Judd
>>
>>
>>
>> "Don Guillett" <[email protected]> wrote in message
>> news:[email protected]...
>> > As usual, you should post your code for comments but
>> > x=cells(rows.count,"a").end(xlup).row
>> > will give you the last row in the column desired so
>> > .copy cells(x,1) will copy to the last row in col A
>> >
>> >
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > [email protected]
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I am trying to record a macro that will pull from several different
>> >> workbooks with active data. For Example: I want to take the data in
>> >> workbook1 column "A" and copy and paste it to column "A" in workbook3,
>> > then
>> >> look at workbook2 and copy and paste the data in column "A" to the
> first
>> >> open cell in workbook3 column "A". It then needs to do the same thing
> for
>> >> column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" &
>> >> "C"
>> > will
>> >> be changing daily. So workbook1 column "A", "B" & "C" will look like
> the
>> >> following.......
>> >>
>> >> Monday...
>> >>
>> >> Workbook1 Workbook2
>> >> Workbook3
>> >> A B C A B C A B C
>> >> 1 4 7 10 1 16 1 4 7
>> >> 2 5 8 11 14 17 2 5 8
>> >> 3 6 9 12 15 18 3 6 9
>> >> 10 1 16
>> >> 11 14 17
>> >> 12 15 18
>> >>
>> >>
>> >>
>> >> Tuesday... after we have made updates the new items are indicated in
> red
>> >>
>> >> Workbook1 Workbook2
>> >> Workbook3
>> >>
>> >> A B C A B C A B C
>> >> 1 4 7 10 1 16 1 4 7
>> >> 2 5 8 11 14 17 2 5 8
>> >> 3 6 9 12 15 18 3 6 9
>> >> 1 3 5 7 9 11 1 3 5
>> >> 2 4 6 8 10 12 2 4 6
>> >> 10 1 16
>> >> 11 14 17
>> >> 12 15 18
>> >> 7 9 11
>> >> 8 10 12
>> >>
>> >>
>> >> Wednesday etc......
>> >>
>> >> The problem I run into when I try to record this macro is that I need
> for
>> > it
>> >> to look in the entire column "A" for any additions and copy all data
> and
>> >> paste it into Column "A" then it needs to look in the entire column
>> >> "B"
>> > and
>> >> paste in column "B" where the last piece of data in column "B" from
>> >> workbook1 stopped. When I have tried to record in the past it will
>> >> copy
>> > all
>> >> of column "A" in workbook1 but when it copies all of "A" in workbook2
> it
>> >> only recognizes where I told it to start previously so it essentially
>> > copies
>> >> over all of the "new" data from column "A" in workbook1.
>> >>
>> >> Any help or guidance would be appreciated.
>> >>
>> >>
>> >> Thanks
>> >>
>> >> Judd
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Then you may desire to change A to B????

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> This code works however it does not copy and paste column "B" in the
> existing workbook into column "C" to the new workbook
>
> Sub Macro1()
> 'If each column has a heading, change the 1's to 2's
> Dim myRange As Range
> Set myRange = Range("A1", Range("A1").End(xlDown))
> Workbooks.Add
> 'Sheets("Sheet1").Activate
> myRange.Copy Range("C1")
> 'Set myRange = Range("B1", Range("B1").End(xlDown))
> 'myRange.Copy Range("C65536").End(xlUp).Offset(1)
> End Sub
>
> Any advice is greatly appreciated.
>
> Judd
>
> "Don Guillett" <[email protected]> wrote in message
> news:[email protected]...
> > try it this way
> > Sub Macro1()
> > 'If each column has a heading, change the 1's to 2's
> > Dim myRange As Range
> > Set myRange = Range("A1", Range("A1").End(xlDown))
> > Workbooks.Add
> > 'Sheets("Sheet1").Activate
> > myRange.Copy Range("C1")
> > 'Set myRange = Range("B1", Range("B1").End(xlDown))
> > 'myRange.Copy Range("C65536").End(xlUp).Offset(1)
> > End Sub
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > <[email protected]> wrote in message
> > news:%[email protected]...
> >> Don,
> >> Ok... This is what I have come up with.
> >>
> >>
> >>
> >>
> >> I need this to look at column "A" Column "B" etc... and copy column "A"
> > just
> >> the data that is in column "A" and paste to Column "C" and do the same
> >> for
> >> column "B" . This data will be added and subtracted to daily so it
must
> >> know where the last item in column "A" is and add it to column "C" on
the
> >> new workbook then copy the data in column "B" and add to the new
workbook
> >> where the data from column "A" left off.
> >>
> >>
> >>
> >> Sub Macro1()
> >>
> >> 'If each column has a heading, change the 1's to 2's
> >>
> >> Dim myRange As Range
> >>
> >> Set myRange = Range("A1", Range("A1").End(xlDown))
> >>
> >> Workbooks.Add
> >>
> >> Sheets("Sheet1").Activate
> >>
> >> myRange.Copy Range("C1")
> >>
> >> Set myRange = Range("B1", Range("B1").End(xlDown))
> >>
> >> myRange.Copy Range("C65536").End(xlUp).Offset(1)
> >>
> >> End Sub
> >>
> >>
> >>
> >> This so far is doing what I need it to do by copying column "A" and
> > opening
> >> a new workbook and adding to colum "C" in this new workbook, and I
think
> > it
> >> is trying to copy "B" but I keep receiving the following message
> >> "Run-time
> >> error 1004 the information cannot be pasted because the copy and paste
> > area
> >> are not the same size and shape"
> >>
> >>
> >>
> >>
> >>
> >> Any Assistance would be greatly appreciated.
> >>
> >>
> >>
> >> Judd
> >>
> >>
> >>
> >> "Don Guillett" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > As usual, you should post your code for comments but
> >> > x=cells(rows.count,"a").end(xlup).row
> >> > will give you the last row in the column desired so
> >> > .copy cells(x,1) will copy to the last row in col A
> >> >
> >> >
> >> >
> >> > --
> >> > Don Guillett
> >> > SalesAid Software
> >> > [email protected]
> >> > <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> I am trying to record a macro that will pull from several different
> >> >> workbooks with active data. For Example: I want to take the data in
> >> >> workbook1 column "A" and copy and paste it to column "A" in
workbook3,
> >> > then
> >> >> look at workbook2 and copy and paste the data in column "A" to the
> > first
> >> >> open cell in workbook3 column "A". It then needs to do the same
thing
> > for
> >> >> column "B" & "C". The data in workbooks 1 & 2 columns "A", "B" &
> >> >> "C"
> >> > will
> >> >> be changing daily. So workbook1 column "A", "B" & "C" will look like
> > the
> >> >> following.......
> >> >>
> >> >> Monday...
> >> >>
> >> >> Workbook1 Workbook2
> >> >> Workbook3
> >> >> A B C A B C A B C
> >> >> 1 4 7 10 1 16 1 4 7
> >> >> 2 5 8 11 14 17 2 5 8
> >> >> 3 6 9 12 15 18 3 6 9
> >> >> 10 1 16
> >> >> 11 14 17
> >> >> 12 15 18
> >> >>
> >> >>
> >> >>
> >> >> Tuesday... after we have made updates the new items are indicated in
> > red
> >> >>
> >> >> Workbook1 Workbook2
> >> >> Workbook3
> >> >>
> >> >> A B C A B C A B C
> >> >> 1 4 7 10 1 16 1 4 7
> >> >> 2 5 8 11 14 17 2 5 8
> >> >> 3 6 9 12 15 18 3 6 9
> >> >> 1 3 5 7 9 11 1 3 5
> >> >> 2 4 6 8 10 12 2 4 6
> >> >> 10 1 16
> >> >> 11 14 17
> >> >> 12 15 18
> >> >> 7 9 11
> >> >> 8 10 12
> >> >>
> >> >>
> >> >> Wednesday etc......
> >> >>
> >> >> The problem I run into when I try to record this macro is that I
need
> > for
> >> > it
> >> >> to look in the entire column "A" for any additions and copy all data
> > and
> >> >> paste it into Column "A" then it needs to look in the entire column
> >> >> "B"
> >> > and
> >> >> paste in column "B" where the last piece of data in column "B" from
> >> >> workbook1 stopped. When I have tried to record in the past it will
> >> >> copy
> >> > all
> >> >> of column "A" in workbook1 but when it copies all of "A" in
workbook2
> > it
> >> >> only recognizes where I told it to start previously so it
essentially
> >> > copies
> >> >> over all of the "new" data from column "A" in workbook1.
> >> >>
> >> >> Any help or guidance would be appreciated.
> >> >>
> >> >>
> >> >> Thanks
> >> >>
> >> >> Judd
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>