[Solved] Copy a portion of one sheet to another sheet.

Discuss the spreadsheet application

[Solved] Copy a portion of one sheet to another sheet.

Postby BarnMgr » Wed Jul 10, 2019 9:39 pm

I have a Calc spreadsheet for the sales for a month. Columns include date, vendor, description, cost, tax, etc. I want to sort by the vendor (AB, TC, 45, 6)name/number and be able to copy just the rows for (AB) to another spreadsheet by itself. I want to keep the column widths the same on the sheet that I copy to. I can copy the entire sheet to another sheet keeping the column widths, but cannot figure out how to copy just a portion of the sheet without losing the column widths. I am able to do this copy and paste function in Excel, so I am thinking there should be a way in Calc. Thank you with any help that you can offer me.
Last edited by robleyd on Thu Jul 11, 2019 4:24 am, edited 2 times in total.
Reason: Move Solved text
Open Office 4.1.6 & /windows 10
BarnMgr
 
Posts: 2
Joined: Wed Jul 10, 2019 9:06 pm

Re: Copy a portion of one sheet to another sheet.

Postby RusselB » Wed Jul 10, 2019 10:36 pm

Welcome to the Forums.
For copying the specific rows (eg: just vendor AB), I'm going to suggest you use Date -> Filter -> Standard Filter.
In the dialog box that comes up there is an option to copy the results to a different location.
I don't know if the column widths will automatically adjust, but I don't think so, as Calc doesn't have an on-the-fly column width adjustment option.
Still, once you have the data copied to the other sheet/location, you can then highlight the columns of that sheet, then right click on one of the column headings and choose the Optimal Column Width option.
Excel does have the on-the-fly column width option, so the column widths in Excel can adjust automatically.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5683
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Copy a portion of one sheet to another sheet.

Postby MrProgrammer » Wed Jul 10, 2019 11:12 pm

BarnMgr wrote:I want to sort by the vendor (AB, TC, 45, 6)name/number and be able to copy just the rows for (AB) to another spreadsheet by itself. I want to keep the column widths the same on the sheet that I copy to. I can copy the entire sheet to another sheet keeping the column widths, but cannot figure out how to copy just a portion of the sheet without losing the column widths.
Method A:
Do what you did, copying the entire sheet. Then use a filter to delete the rows on the new sheet which you don't want. Data → Filter → Standard Filter → Field:Vendor → Condition:<> → Value:AB → OK. Select all the visible data rows (the AB rows are hidden) and Edit → Delete Cells → Delete entire rows. Then click on the first row (column headers) and remove the filter. Read section 7. Analyzing and modifying your data in Ten concepts that every Calc user should know.

Method B:
Copy the rows you want to the new sheet, presumably using a filter, but however you like. In the original sheet, remove any filter so all rows are displayed. Select the columns whose widths you want to copy by selecting their column names (A, B, C, …) above the cells. Edit → Copy. In the new sheet, select the cell in the first row of the leftmost column to adjust. Edit → Paste Special → Paste All → Text → Numbers → Date & Time → Formats → OK. This pastes the column format (which includes the width) and nothing else.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3896
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Copy a portion of one sheet to another sheet. - Solved

Postby BarnMgr » Thu Jul 11, 2019 1:58 am

Thank you for that info. I was doing something like that but not quite as clean.
Open Office 4.1.6 & /windows 10
BarnMgr
 
Posts: 2
Joined: Wed Jul 10, 2019 9:06 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 41 guests