Take rows from one sheet, put into other based on other sht

macdian

New Member
Joined
Dec 10, 2003
Messages
24
Hi,

I've got a very interesting problem.

I've got 2 workbooks:

#1)Is a massive listing of accounts with unique account numbers in column A, and various other data pertaining to each account in about the next 7 columns

#2)Is a workbook with about 8 different sheets, all with specific names of "groupings" that the accounts from workbook 1 should be placed into, and each sheet has a list of the account numbers which make up that grouping.

What I am trying to do is get a macro that will separate workbook #1 into the groupings as per workbook 2. I want a separate workbook (or worksheet, whichever is easiest for me and the code)

Also, if the macro finds an account number in workbook 1 that is not in workbook 2, is it possible to generate a list of these?

I really appreciate anyone who can help me. I know this is possible, I just really don't know where to start.

Thanks,

Macdian
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Thinking out loud --

Open Book2 and Book1.
For each sheet in Book2, do this:
1. Copy the list of relevant accounts
2. Switch to Book1, paste these accounts into cell Z2 (Z1 has Account Number for example -- make it EXACTLY the same as the heading in the big table.
3. Use Advanced Filter to filter for those accounts, using Z1:Z whatever as the Criteria.
4. Select the whole table, Edit | Go To | Special | Visible Cells only (ALT + E G S Y)
5. Copy and paste back to Book2

Repeat.

This can be automated with a bit more information.
Where is the account list in Book2 -- always the same location in the sheet?
Where in the sheet do you want to paste?
How many columns of data are there in Book1?

Denis
 

macdian

New Member
Joined
Dec 10, 2003
Messages
24
Hi Denis,

Thanks for the reply. That might actually work. However, I'd like to automate it for sure.

Right now, I don't have access to the sheets, so I can't remember exactly how many columns book 1 has, so I'm going to say it has 5 columns of data for now, which I'm sure can easily be changed if that's wrong.

To make things simple, I was thinking that for each sheet in book 2, (the account numbers), I would change the sheet so that they are all in column A, beginning in row one. The only identifier would be the sheet name (which is currently two letters, for example, SG). However, if need be, the sheet name could also be in the first row.

For pasting, I was also thinking the simpler the better, paste in row 1, column A.

You think it's easy to automate this?

Thanks again,

Macdian
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi macdian, try this for a first shot. I created a Source book with this layout:
MoveAccountsSource.xls
ABCDEFGHIJKLMNOP
1AccountIdentifierHeading 1Heading 2Heading 3Heading 4Heading 5Heading 6IdentifierDestination:MoveAccountsDest.xls
20001SG325347555965418152132823SG
30002SA289117103728440717145325
40003SC201624372162184956845120
50004SD526746034123194248632802
Sheet1

Assumptions --
Destination and Source books are in the same folder, Destination book is closed before running routine
Destination book's name (with .xls extension) is in cell M1 of main data sheet in Source book
Main data table has at least 1 blank column and 1 blank row separating it from the Criteria info on the sheet
Main sheet in Source file is Sheet1. You'll have to adjust code to suit.
With those assumptions in mind, copy this code to a new module in the Source workbook (Alt + F11, Insert | Module) and return to Excel.
Press Alt + F8 to show list of macros, double-click the macro name.
Code:
Sub TransferData()
  Dim SourceBook As Workbook, DestBook As Workbook
  Dim Sht As Worksheet
  Dim sFilter As String
  
  Set SourceBook = ActiveWorkbook
  Workbooks.Open ActiveWorkbook.Path & "\" & Sheets("Sheet1").Range("N1").Value
  Set DestBook = ActiveWorkbook
  
  For Each Sht In DestBook.Sheets
    sFilter = Sht.Name
    With SourceBook.Sheets("Sheet1")
      .Activate
      .Range("K2").Value = sFilter
      On Error Resume Next
      .ShowAllData
      On Error GoTo 0
      .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("K1:K2"), Unique:=False
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=DestBook.Sheets(sFilter).Range("A1")
    End With
  Next Sht
  SourceBook.Sheets("Sheet1").Activate
  ActiveSheet.ShowAllData
End Sub
Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,122,712
Messages
5,597,717
Members
414,167
Latest member
morebytes

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
Top