I have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet. For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.
Copying Specific Columns Into A New Worksheet
-
-
-
Re: Copying Specific Columns Into A New Worksheet
what about copying all cells into a new sheet and then deleting what you don't need? You can start by recording a macro that does this, then edit it if needed.
-
Re: Copying Specific Columns Into A New Worksheet
Code
Display MoreSub CopyWantedColumns() Dim strFind As String, rFound As Range Dim lLoop As Long On Error Resume Next 'Remove to debug For lLoop = 1 To 3 'Adjust to suit strFind = Choose(lLoop, "Accounts", "Name", "Age") 'Adjust to suit With Sheet1 'CODENAME Set rFound = .Rows(1).Find(What:=strFind, After:=.Cells(1, 1), LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) rFound.EntireColumn.Copy Destination:=Sheet2.Cells(1, .Columns.Count).End(xlToLeft)(1, 2) End With Next lLoop On Error GoTo 0 End Sub
-
Re: Copying Specific Columns Into A New Worksheet
Ok so brouz... i can't do it that way because when you record the macro, it only realizes the exact columns i choose, so for example if property name is in column B in one data sheet and i record the whole macro, it will only work if property name is always in column B... but this is not the case, some times is column b sometimes c etc....
Dave Hawley... I tried to use the VB code you suggested and nothing happens.. Am i supposed to edit this in some way... I am fairly new to this VB stuff so any explanation might help
-
Re: Copying Specific Columns Into A New Worksheet
Using Dave's code, plug in your field names at this line in place of Accounts, Name, Age (those were examples):
With that, based on your first post, it might look something like this:
Code
Display MoreSub CopyWantedColumns() Dim strFind As String, rFound As Range Dim lLoop As Long On Error Resume Next 'Remove to debug For lLoop = 1 To 3 'Adjust to suit strFind = Choose(lLoop, "property type", "loan balance current", "amortization type") 'Adjust to suit With Sheet1 ' CODENAME Set rFound = .Rows(1).Find(What:=strFind, After:=.Cells(1, 1), LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) rFound.EntireColumn.Copy Destination:=Sheet2.Cells(1, .Columns.Count).End(xlToLeft)(1, 2) End With Next lLoop On Error GoTo 0 End Sub
and, furthermore, if you are looking for more than 3 columns, you'll need to adjust this line:
try that out and if you can't get it to work. Attach an example workbook with your code in it.
-
-
Re: Copying Specific Columns Into A New Worksheet
Ok so I did exactly what you suggested and the macro doesn't run at all.. nothing happens.. I have the code from above, with the different names of the columns and changed the number from 3 to 27 (thats how many columns i need) and nothing happens at all... can you see a reason why the macro won't run at all?
-
Re: Copying Specific Columns Into A New Worksheet
can you attach an example workbook?
-
Re: Copying Specific Columns Into A New Worksheet
I would love to but the files are too big... the site won't let me attach anything bigger than 48kb... these files are like 480kb... unless there is another way to do it i don't know of
-
Re: Copying Specific Columns Into A New Worksheet
just carve out an example. leave all columns, but maybe only 20 rows or so.
-
Re: Copying Specific Columns Into A New Worksheet
i cut out everything in the entire worksheet except for the column headers and its still 400+ kb.. not sure what else i can do.. very frustrating
-
-
Re: Copying Specific Columns Into A New Worksheet
It works for me. I attached an example illustrating that Dave's code works flawlessly. This places the 3 example columns in Columns B, C, D of Sheet 2.
note that this code assumes that you are taking data from Sheet1 and placing it in Sheet2, if that's not the case, then a minor tweak will fix it.
-
Re: Copying Specific Columns Into A New Worksheet
I see that this example seems to work... I can't tell you why it doesnt work for my data file.... the example is a very straightforward.. my data is much more complex, maybe that is why it doesn't seem to work.. ill mess around with it and see what I can do... thanks though
-
Re: Copying Specific Columns Into A New Worksheet
I was able to break my file into two seperate files.... if you can, just copy and past "part2" next to the last blank column in "part1"... once you do this, it will be the complete data worksheet I am dealing with.. maybe you can figure out why or how I can solve this problem now b/c i have tried and cannot do it... thanks
-
Re: Copying Specific Columns Into A New Worksheet
I pasted the 2 together... now, excactly which columns are you trying to copy to another sheet? if i have the list, i'll give it a try.
-
Re: Copying Specific Columns Into A New Worksheet
I need the column names and all the data below them to be pasted as well... good luck...
"PropertyName"
"Watchist"
"Loan Balance Secur"
"Loan Balance Current"
"% of deal"
"Current Rate"
"Orig IO Per"
"Cross Coll #"
"Amortization Type"
"Day Count Basis"
"Defeased Status"
"Dlq Status"
"# Times Known Dlq 12 Months"
"# Times Known Dlq Loan Life"
"State"
"NormalizedProp Type"
"Maturity Date"
"NOI/NCF and DSCRs NOI/NCF"
"NOI/NCF and DSCRs Secur NOI"
"NOI/NCF and DSCRs Secur NCF"
"NOI/NCF and DSCRs DSCR"
"NOI/NCF and DSCRs Secur DSCR (NOI)"
"NOI/NCF and DSCRs Secur DSCR (NCF)"
"Appraisal and LTVs LTV"
"Appraisal and LTVs Secur LTV"
"Occupancy Rate"
"Occupancy Secur Occ"
"Year Built" -
-
Re: Copying Specific Columns Into A New Worksheet
OK...so... i *think* (not 100% sure) that the problem is with Carriage Returns in the header text. I cleaned them up using code from Jack in the UK, i found on Here: http://%22http//www.ozgrid.co….php?t=39246%22
I then adapted Dave's code again using your criteria. The code definitely works, i think the issue is the data you are importing. Is this coming in as a CSV file? Anyway, note also that this carriage return cleanup deleted spaces, so i modified your headers (example "Year Built" became "YearBuilt")
This code is copying columns from "Part1" to "Sheet2", modify your sheet names as needed:
Code
Display MoreSub MikeyV() Dim strFind As String, rFound As Range Dim lLoop As Long On Error Resume Next For lLoop = 1 To 28 strFind = Choose(lLoop, "PropertyName", "Watchist", "Loan BalanceSecur", _ "Loan BalanceCurrent", "% ofdeal", "CurrentRate", "OrigIO Per", "CrossColl #", _ " AmortizationType", "Day CountBasis", "DefeasedStatus", "DlqStatus", _ "# TimesKnown Dlq12Months", "# TimesKnown DlqLoanLife", "State", _ "NormalizedProp Type", "MaturityDate", "NOI/NCF and DSCRsNOI/NCF", _ "NOI/NCF and DSCRsSecurNOI", "NOI/NCF and DSCRsSecurNCF", "NOI/NCF and DSCRsDSCR", _ "NOI/NCF and DSCRsSecurDSCR(NOI)", "NOI/NCF and DSCRsSecurDSCR(NCF)", _ "Appraisal and LTVsLTV", "Appraisal and LTVsSecurLTV", "OccupancyRate", _ "OccupancySecurOcc", "YearBuilt") With Sheets("Part1") Set rFound = .Rows(1).Find(What:=strFind, After:=.Cells(1, 1), LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) rFound.EntireColumn.Copy Sheets("Sheet2").Cells(1, .Columns.Count).End(xlToLeft)(1, 2) End With Next lLoop On Error GoTo 0 End Sub
-
Re: Copying Specific Columns Into A New Worksheet
Ok I will try this code and see how it works... the data is coming in as a CSV file and I have to convert it to an excel file. just to be clear though, you say i have to use a different code first to "clean it up" and then use this code?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I just tried using this code and nothing happens at all... the macro doesnt even run once i open my data sheet.. what is the code for "cleaning up" the carraige returns or whatever??
-
Re: Copying Specific Columns Into A New Worksheet
for Data Cleanup, i noticed when i cleared all formats from your sheet (or you can un-wrap the header line) and you'll see the little boxes. That made me wonder if the macro could not find the exact match because of these carriage returns.
i posted the hyperlink above, from the thread "Dealing with Carriage Returns" - read it for reference, and the code is below. You add a new header line and use this function.
Code
Display MoreFunction JR_CleanCells(JR_Text As Variant) '------------------------------------------------------------------ 'Procedure Name(VBA) : JR_CleanCells ' Date & Time : Date: Monday 5 September 2005 & Time: 00:17 'Code Author : Written by: Jack in the UK 'Our Web Site : www.excel-it.com 'Purpose : www.OzGrid.com - Forum Post 'Original Posts Link: Dealing with Carriage Returns 'Post Link : http://www.ozgrid.com/forum/newreply...reply&p=195829 'Comments : Cell clean for ASCII characters 'Function Return Type: Variant '------------------------------------------------------------------- Dim vResult As Variant Dim myReplaceWith$ myReplaceWith = "" ' jiuk - edit as needs be Application.Volatile True If IsMissing(JR_Text) Then Exit Function End If vResult = Trim(JR_Text) vResult = Application.WorksheetFunction.Clean(vResult) vResult = Application.WorksheetFunction.Substitute(vResult, Chr(10), myReplaceWith) vResult = Application.WorksheetFunction.Substitute(vResult, Chr(13), myReplaceWith) '// jiuk - add more as needs be JR_CleanCells = vResult End Function
also, Comment the 3rd line of the procedure so that it will show the runtime errors if they occur:
-
-
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!