If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
importing specific data from one spread sheet into a new spreadshe
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!! |
#2
|
|||
|
|||
importing specific data from one spread sheet into a new spreadshe
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" wrote: 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!! |
#3
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
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 "Joel" wrote: 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" wrote: 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!! |
#4
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
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:P,... 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AllieR" wrote: 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 "Joel" wrote: 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" wrote: 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!! |
#5
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
Thanks, Shane -
The columns for the data i listed in the previous post a 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! "Shane Devenshire" wrote: 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:P,... 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AllieR" wrote: 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 "Joel" wrote: 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" wrote: 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!! |
#6
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
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" wrote: Thanks, Shane - The columns for the data i listed in the previous post a 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! "Shane Devenshire" wrote: 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:P,... 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AllieR" wrote: 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 "Joel" wrote: 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" wrote: 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!! |
#7
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
WOW! Thank you so much - i really appreciate it!
allison "Joel" wrote: 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" wrote: Thanks, Shane - The columns for the data i listed in the previous post a 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! "Shane Devenshire" wrote: 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:P,... 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AllieR" wrote: 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 "Joel" wrote: 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" wrote: 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!! |
#8
|
|||
|
|||
importing specific data from one spread sheet into a new sprea
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 "Joel" wrote: 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" wrote: Thanks, Shane - The columns for the data i listed in the previous post a 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! "Shane Devenshire" wrote: 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:P,... 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "AllieR" wrote: 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 "Joel" wrote: 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" wrote: 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!! |
Thread Tools | |
Display Modes | |
|
|