importing specific data from one spread sheet into a new spreadshe

[AllieR]

I have a spreadsheet with multiple columns of info - I want to be able to
pull certain columns into a new spread sheet without cutting and pasting
them. is there any way to just import specified columns? Like in a mail merge
in MS word? THANKS!!

 

[Joel]

I fyou are doing the same operations numerous times then it is a good idea to
write a macro. Let me know which columnns you are bring from the old sheet
to the column in the new sheet. It is pretty simple to do.

 

[AllieR]

okay - i'll try to explain! Thanks, btw for taking the time to help me!

the spreadsheet is a daily report for 5 Income tax preparers offices. the
data comes from a program called crosslink. i'd be in the clear if i could
just pull the columns of info i wanted from crosslink to excel...but i can't
specify certain columns. so i can pull all the data into a spreadsheet, and
would then want to be able to pull the following information into a new
spread sheet:

Site
Receipt Number
Primary Last Name
Refund Type
Paid Preparer Name
Prep Fee
ELF Fee
Doc Fee
Total Fee

This would be done every day, as part of the daily report (it was being
keyed in by hand before, so this would be a lot more accurate. )

Then, i'd want to make worksheets for each office.

does this make sense???

thanks, allison

 

[Shane Devenshire]

Hi,

To program this we would need to know what column you want to bring from the
download to each sheet. Their names are helpful but we also need to know
which columns they are A, C, M,...

Also, since you want to produce a separate sheet for each "office" we need
to know how you determine the office. For example can you run the Data,
Filter, AutoFilter command on the "Site" field to display only records for a
specific "office"?

The general approach would be to use the Data, Filter, Advanced Filter
command copy to a new location option. Or it may be possible to use a pivot
table.

 

[AllieR]

Thanks, Shane -

The columns for the data i listed in the previous post are:

Site = B
Receipt Number= C
Primary Last Name = E
Refund Type = J
Paid Preparer Name = M
Prep Fee = N
ELF Fee = O
Doc Fee = P
Total Fee= Q


You're right about doing a sort each office - that is what the "site" column
is- it's the office name - so it could be sorted and info on the other
offices (that isn't necessary for that particular sheet) can be hidden. I
think I could get that part under control if I could just get the data that i
need into a master spread sheet!

 

[Joel]

The code below will open a dialog box to select the file to import. It will
clear the master sheet and add the columns you specified to the master sheet.
Then it will move the data to the appropriate Site sheet appending the data
after any previous data that existed. The code will automatically create a
"Master" Sheet and the Site sheets if they don't exist.

The code assumes there is a header row in the source file.

Sub GetData()
Found = False
For Each sht In ThisWorkbook.Sheets
If sht.Name = "Master" Then
Found = True
Exit For
End If
Next sht
If Found = False Then
With ThisWorkbook
Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = "Master"
End With
End If

Set MasterSht = ThisWorkbook.Sheets("Master")
'Clear worksheet
MasterSht.Cells.ClearContents


filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Cannot OPen File, Exiting Macro")
Exit Sub
End If

Set DataBk = Workbooks.Open(Filename:=filetoopen)

With DataBk.ActiveSheet
'Site = B to Column A
.Columns("B").Copy _
Destination:=MasterSht.Columns("A")

'Receipt Number = C to column B
.Columns("C").Copy _
Destination:=MasterSht.Columns("B")

'Primary Last Name = E to column C
.Columns("E").Copy _
Destination:=MasterSht.Columns("C")

'Refund Type = J to column D
.Columns("J").Copy _
Destination:=MasterSht.Columns("D")

'Paid Preparer Name = M to column E
.Columns("M").Copy _
Destination:=MasterSht.Columns("E")

'Prep Fee = N to column F
.Columns("N").Copy _
Destination:=MasterSht.Columns("F")

'ELF Fee = O to column G
.Columns("O").Copy _
Destination:=MasterSht.Columns("G")

'Doc Fee = P to column H
.Columns("P").Copy _
Destination:=MasterSht.Columns("H")

'Total Fee = Q to column I
.Columns("Q").Copy _
Destination:=MasterSht.Columns("I")

End With

DataBk.Close savechanges:=False


With MasterSht
'Sort Data by Site in column A
'Assume Row 1 is header row
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
key1:=.Range("A"), _
Order:=xlAscending, _
header:=xlYes

RowCount = 2
FirstRow = RowCount
Do While .Range("A" & RowCount) <> ""
'Test if adjacent row are from different sites
If .Range("A" & RowCount) <> _
.Range("A" & (RowCount + 1)) Then

Site = .Range("A" & RowCount)
'test if sheet with Site Name already exists
With ThisWorkbook
Found = False
For Each sht In .Sheets
If sht.Name = Site Then
Found = True
Exit For
End If
Next sht

If Found = False Then

Sheets.Add after:= _
.Sheets(.Sheets.Count)
ActiveSheet.Name = Site
Set SiteSht = .Sheets(Site)
'copy header row to new sheet
MasterSht.Rows(1).Copy _
Destination:=SiteSht.Rows(1)
NewRow = 2
Else
Set SiteSht = .Sheets(Site)
LastRow = _
SiteSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End If
End With

'Copy New Data to site Sheet
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=SiteSht.Rows(NewRow)

NewRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[AllieR]

WOW! Thank you so much - i really appreciate it!
allison

 

[AllieR]

Thanks again, Joel...this may be a stupid question, but what do I do with the
code?? I tried a couple of different things, nothing worked. Any chance you
could walk me through where it goes, and how to make it work?? thanks a
BILLION!

allison