•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How Do I Copy a Row to Another Sheet in Workbook Based on a Value in Column?
Our take
Struggling to copy rows from your master sheet to specific item code tabs based on conditions can be frustrating, especially when you're trying to maintain historical data. Fortunately, there's a way to automate this process using formulas or scripts that copy relevant information without overwriting existing entries. In this guide, we’ll explore methods to efficiently transfer data from your source sheet to target sheets based on column values, ensuring that you avoid duplicates and preserve your records even when the source sheet resets.
I've looked at the Google machine and have not found an answer to my situation.
Situation: I have a master sheet (source sheet) that is used for tracking ordering information (date of order, date shipped, sequential order number, number of items, and the item code). This sheet is cleared every year and the order numbers restart at 1. The other sheets in the workbook are the tabs for each item code (target sheets) where I track the ordering information for that item. Those tabs are used to track over multiple years.
Goal: Copy data in source sheet row from columns C,D,E,F,H,I,J,K to the item code sheet (target sheet) if column G contains the item code. I would like it to search all of the source sheet so that if a line in the source sheet gets added above the last line, it pulls it into the target sheet, but I would like to avoid it pasting duplicate lines if possible. I would also like it to not erase what was copied to the target sheet once the source sheet is restarted at the beginning of the year. Does anyone know if this is possible?
I tried using =filter, but that erased the data when the source sheet was erased and it would be a lot of work to copy and paste as text everything in each target sheet before restarting the source sheet. I then tried a VBA script for each target sheet, but it only copied the last line of the relevant source, which was not helpful when there is prior info already logged. Example of what my workbook is like
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to link data from specific columns on one sheet to another, then sort it column A alphabetically and match the other columns based on corresponding data from Sheet 1I am a beginner at attempting to write formulas in Excel, and I am currently using Excel on a web browser mainly but can and do occasionally edit in the desktop version. I believe the version is Microsoft Office LTSC Professional Plus 2024 in the desktop version, or at least that's the product activated. I have two sheets on the same spreadsheet, and I want to pull in the data from 4 different columns to the second sheet and have it automatically update when there are changes or additions to the first sheet. I have a couple issues making this happen - the main one is that the data in my first sheet is custom sorted by color, but I want the new sheet to take the data from column B on sheet 1 and sort it alphabetically, and take the other columns and match them correspondingly to the correct values they match to from column B on the first sheet. Secondly, for blank cells, I want it to leave them blank instead of inputting zero, or any dates. I don't know if this is all even possible, or if I should just remove my custom filters from sheet 1 by cell color and just link it that way. I've spent hours trying to find formulas that work. Please see attached images for reference on what I've tried, and for more clarification as to exactly what I'm requesting. Sorry for the copious redacting, I wanted to be safe rather than sorry with identifiable info regarding this. Any help would be greatly appreciated. https://preview.redd.it/6vt2ocasj9kg1.png?width=1199&format=png&auto=webp&s=35a78bf5c6d96437094c04023d3ca24fa6ec3399 https://preview.redd.it/kqbi5za5k9kg1.png?width=791&format=png&auto=webp&s=f80ee9272131546132c6925e5d0a8b9dc3ae74f2 https://preview.redd.it/1947drz7k9kg1.png?width=836&format=png&auto=webp&s=4c07c1354c348ee85b1a556d21dd0c677b1ae659 https://preview.redd.it/8ynxkp39k9kg1.png?width=869&format=png&auto=webp&s=7d69705a4a61702e0f89a49f485f8cf53d230146 https://preview.redd.it/7fd5p2v9k9kg1.png?width=943&format=png&auto=webp&s=cd3c9fe721db57b44f7b5260ac891641a80170a0 submitted by /u/MeretrixRex [link] [comments]
- Copying and pasting from templateI want to build some VBA that lets me Copy and Paste (As special values) from one template to another. The source template looks *extremely* similar to the destination template, but the destination template may add columns before the data I want to copy and paste without the source template doing the same. How would you approach this? I can see Power query or Identifier Columns being the best bet, but both source and destination already have a decent bit of chug on them, so trying to keep as light as possible. TLDR; Want to Hard Paste (can’t have a link to an external book at the end) Columns B-K from source workbook sheet1 into D-M of recipient workbook sheet1 . Source B-K may become C-L and Recipient D-M may become E-N at any moment and want the code to not worry about that. Feels like It should be super simple but can't think of the most efficient way to do it submitted by /u/Codenamerondo1 [link] [comments]
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]