Copy Dynamic Range in Data Workbook to Another workbook

S

sugoimm

New Member
Joined
Aug 2, 2017
Messages
3
Hello,

What I am trying to do is copy a "Set" of data from one workbook ("SourceWorkbook") to another workbook that contains an invoice template sheet ("TargetWorkbook").

I will now define what I believe is necessary in order to understand what I am trying to accomplish.

SourceWorkbook - Contains a single sheet with data. There are 7 columns. Name, Account #, Value, Billing Date, Billing Period, Fee, % Fee. The data is sorted by Column labeled name. The Data contains what I refer to as a Set which needs to be copied to the TargetWorkBook on a a new sheet ( one sheet per set) and said sheet should have the copy and pasted Values, forumas, formates (ALL) of the contents in the TemplateSheet found in the TargetWorkBook.

Set - The set refers to the data that would to to copy from the SourceWorkbook to the TargetWorkbook. A set can be a single row or multiple rows ( row number is dynamic and need to be "Looked For") . The columns in the set will always be the Range A to C. What defines the Row Range of the set is that the values of Column A the same. Once Column A is different from the previous cell value of Column A then there is a new Set, Which implies that a new sheet needs to be created on the TargetWorkBook and that sheet should have the contents of the TemplateSheet copied into it and that set should be copy and pasted on to the new sheet starting on cell A17.

Worded differently, what I believe is most difficult is having the macro find the set. It has to recognize that the value from the previous cell in column A is different and then look for the same values of that different set. Meaning, find all the duplicate values of column A, and there may be non if there is only one account per name, and then select all the duplicate values ( rows depend on the number of duplicate values and columns, again, will always be A to C) and be ready to copy them to the new sheet etc.

TemplateSheet -a sheet inside the TargetWorkbook which is copied onto a new sheet ( and a new sheet is created when there is a new set).

The macro ends once there is a blank cell in Columns A.

Examples: SorceWorkbook Data


[TABLE="width: 320"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Account
[/TD]
[TD]Billable Value
[/TD]
[TD]Bill Date
[/TD]
[TD]Bill Period
[/TD]
[/TR]
[TR]
[TD]Tim
[/TD]
[TD]111
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Tod
[/TD]
[TD]112
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Tod
[/TD]
[TD]113
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Tod
[/TD]
[TD]114
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Kim
[/TD]
[TD]115
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Kim
[/TD]
[TD]116
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
</tbody>[/TABLE]

Example of a sets in this data:
Set 1:
[TABLE="width: 320"]
<tbody>[TR]
[TD]Tim
[/TD]
[TD]111
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
</tbody>[/TABLE]
Set 2:
[TABLE="width: 320"]
<tbody>[TR]
[TD]Tod
[/TD]
[TD]112
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Tod
[/TD]
[TD]113
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Tod
[/TD]
[TD]114
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
</tbody>[/TABLE]
Set 3:


[TABLE="width: 320"]
<tbody>[TR]
[TD]Kim
[/TD]
[TD]115
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
[TR]
[TD]Kim
[/TD]
[TD]116
[/TD]
[TD]10.00
[/TD]
[TD]17-Jun
[/TD]
[TD]05/01/17-05/31/17
[/TD]
[/TR]
</tbody>[/TABLE]


Now this set need to be copy and pasted into Cell A17 of a new sheet in the TargetWorkBook and the new sheet needs to have the copy and pasted (all) of the TemplateSheet in it.
This repeated until there is no more data.

Thank you and let me know if I need to provide any additional information.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Do you mean you want to copy only column A:C of each data set?
Try this:

Code: 
[COLOR=blue]Sub[/COLOR] a1195093b[B]()[/B]
[COLOR=blue]Dim[/COLOR] r [COLOR=blue]As[/COLOR] Range[B],[/B] va[B],[/B] ra [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] rb [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] ws1 [COLOR=blue]As[/COLOR] Worksheet[B],[/B] FoundCell [COLOR=blue]As[/COLOR] Range
[COLOR=blue]Dim[/COLOR] wb2 [COLOR=blue]As[/COLOR] Workbook
 
 
Application.ScreenUpdating [B]=[/B] [COLOR=blue]False[/COLOR]
[COLOR=blue]Set[/COLOR] wb2 [B]=[/B] Workbooks[B]([/B][COLOR=brown]"TargetWorkBook"[/COLOR][B])[/B]
 
[COLOR=blue]Set[/COLOR] r [B]=[/B] Range[B]([/B][COLOR=brown]"A2"[/COLOR][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B]))[/B]
va [B]=[/B] r
 
[COLOR=blue]Set[/COLOR] d [B]=[/B] CreateObject[B]([/B][COLOR=brown]"scripting.dictionary"[/COLOR][B])[/B]
d.CompareMode [B]=[/B] vbTextCompare [I][COLOR=seagreen]'vbBinaryCompare[/COLOR][/I]
 
   [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
      d[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))[/B] [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B]
   [COLOR=blue]Next[/COLOR]
  
   [COLOR=blue]Set[/COLOR] ws1 [B]=[/B] ActiveSheet
   wb2.Activate
   
   [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] x [COLOR=blue]In[/COLOR] d
       
        [COLOR=blue]Set[/COLOR] FoundCell [B]=[/B] r.Find[B]([/B]x[B],[/B] SearchDirection[B]:=[/B]xlNext[B])[/B]
        [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] FoundCell [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
           
            [I][COLOR=seagreen]'the first row of x[/COLOR][/I]
            ra [B]=[/B] FoundCell.row
            [I][COLOR=seagreen]'find the last row of x[/COLOR][/I]
            rb [B]=[/B] r.Find[B]([/B]x[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
           
            wb2.Sheets[B]([/B][COLOR=brown]"TemplateSheet"[/COLOR][B]).[/B]Copy After[B]:=[/B]Sheets[B]([/B]Sheets.count[B])[/B] [I][COLOR=seagreen]'creates a new worksheet[/COLOR][/I]
            ws1.Range[B]([/B][COLOR=brown]"A"[/COLOR] [B]&[/B] ra[B]).[/B]Resize[B]([/B]rb [B]-[/B] ra [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B][COLOR=crimson]3[/COLOR][/B][B]).[/B]Copy Sheets[B]([/B]Sheets.count[B]).[/B]Range[B]([/B][COLOR=brown]"A17"[/COLOR][B])[/B]
 
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
   
    [COLOR=blue]Next[/COLOR]
 
Application.ScreenUpdating [B]=[/B] [COLOR=blue]True[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
Upvote 0
@ Akuini This is fantastic! Thank you for the help.

I'm new to VBA. Can you explain the following portion of code that you wrote.

Code: 
For Each x In d

How does the x work if it is never defined? Is there an x in each directory at the beginning so you are searching for each x then selecting back to find the previous x?

Also the code :

Code: 
For i = 1 to UBound(va,1)
                  d(va(i, 1)) =1
           Next

How does this work? Why is the directory equal to 1 at the end? Does this define each directory start and end?

You have given me the solution already so do not feel the need to go back and answer these questions. Again, much thanks.
 
Upvote 0
Actually I'm not good at explaining things, partly because English is not my native language.
Sorry I forgot to declare some variable, it should be like this:

Code: 
[COLOR=blue]Sub[/COLOR] a1195093b[B]()[/B]
[COLOR=blue]Dim[/COLOR] r [COLOR=blue]As[/COLOR] Range[B],[/B] va[B],[/B] ra [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] rb [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] ws1 [COLOR=blue]As[/COLOR] Worksheet[B],[/B] FoundCell [COLOR=blue]As[/COLOR] Range
[COLOR=blue]Dim[/COLOR] wb2 [COLOR=blue]As[/COLOR] Workbook
[COLOR=blue]Dim[/COLOR] x[B],[/B] d [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
 
Application.ScreenUpdating [B]=[/B] [COLOR=blue]False[/COLOR]
[COLOR=blue]Set[/COLOR] wb2 [B]=[/B] Workbooks[B]([/B][COLOR=brown]"TargetWorkBook"[/COLOR][B])[/B]
 
[COLOR=blue]Set[/COLOR] r [B]=[/B] Range[B]([/B][COLOR=brown]"A2"[/COLOR][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B]))[/B]
va [B]=[/B] r   [I][COLOR=seagreen]'populate range value into array va[/COLOR][/I]
 
[COLOR=blue]Set[/COLOR] d [B]=[/B] CreateObject[B]([/B][COLOR=brown]"scripting.dictionary"[/COLOR][B])[/B]
d.CompareMode [B]=[/B] vbTextCompare [I][COLOR=seagreen]'vbBinaryCompare[/COLOR][/I]
 
   [COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
      d[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))[/B] [B]=[/B] [COLOR=brown]1[/COLOR] [I][COLOR=seagreen]'populate array va to d (dictionary object) to create unique list of the names[/COLOR][/I]
   [COLOR=blue]Next[/COLOR]
  
   [COLOR=blue]Set[/COLOR] ws1 [B]=[/B] ActiveSheet
   wb2.Activate
   
   [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] x [COLOR=blue]In[/COLOR] d [I][COLOR=seagreen]'iterating each key (name) in d[/COLOR][/I]
       
        [COLOR=blue]Set[/COLOR] FoundCell [B]=[/B] r.Find[B]([/B]x[B],[/B] SearchDirection[B]:=[/B]xlNext[B])[/B]
        [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] FoundCell [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
           
            [I][COLOR=seagreen]'the first row of x[/COLOR][/I]
            ra [B]=[/B] FoundCell.row
            [I][COLOR=seagreen]'find the last row of x[/COLOR][/I]
            rb [B]=[/B] r.Find[B]([/B]x[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
           
            wb2.Sheets[B]([/B][COLOR=brown]"TemplateSheet"[/COLOR][B]).[/B]Copy After[B]:=[/B]Sheets[B]([/B]Sheets.count[B])[/B] [I][COLOR=seagreen]'creates a new worksheet[/COLOR][/I]
            ws1.Range[B]([/B][COLOR=brown]"A"[/COLOR] [B]&[/B] ra[B]).[/B]Resize[B]([/B]rb [B]-[/B] ra [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B][COLOR=crimson]3[/COLOR][/B][B]).[/B]Copy Sheets[B]([/B]Sheets.count[B]).[/B]Range[B]([/B][COLOR=brown]"A17"[/COLOR][B])[/B]
 
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
   
    [COLOR=blue]Next[/COLOR]
 
Application.ScreenUpdating [B]=[/B] [COLOR=blue]True[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

I used Dictionary object to get unique values of the names.
Dictionary has 2 part: key & item

This part 'd(va(i, 1)) = 1' says, for example: add Tim as key & ‘1’ as item
Actually the ‘1’ part can be anything or blank, I used ‘1’ just for convenience. We won’t use it in this code.

This part: ‘For Each x In d’ means iterating each key (name) in d.

You may find this link useful to learn about dictionary object:
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
343
JMWh1t3
J
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
214
howard
H
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
949
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top