How to create a macro which will copy data from row to column, using conditions?
I am using currently v lookup to find and place values against the specific item. however, I am looking for help for a VB macro which will out the data in defined outcome.
please see 1st screen shot of raw data
second screen shot, should be the outcome.
Please note the "site" is not constant it can be any value, so I have put all site in column A .
currently V look is doing the job well. but makes the file crash sometime.
You can solve this with a Pivot Table using your original data source with NO changes in the table layout.
Drag the columns as shown below (you'll want to rename them from the default names): For
Columns, drag the
Date field there first. The
Σ Values field will appear after you've dragged two Fields to the Values area, and should be below
And with some formatting changes from the default, the result can look like:
VBA to Copy and Paste Rows if Condition is Met, While I have seen actions that will copy all data from one sheet to Based on the payment type (condition of Column A in "Operations - Data" sheet), If you haven't already created a MODULE, click on the menu bar up top You will paste the macro into the module window on the right side of the screen. Copy & Insert Row / Column. Instead you can insert the copied row or column and shift the existing rows or columns to make room. This will copy row 1 and insert it into row 5, shifting the existing rows down:
Can you change your source data? If you change your data to look like the table "Changed Source Data" below you can solve your issue with a pivot table.
Solution with a Pivot Table
Changed Source Data
Excel VBA to Copy and Paste Rows if multiple Conditions is Met , By “Excel VBA Copy Methods”, I mean the different methods you can use to copy In this test, I filtered by rows that contain a given first name in the first column. Once you create the table it pretty easy to display the data in many different ways. ExcelMacroMastery.com ' Author : Paul Kelly ' Purpose : Turn off automatic Spreadsheets are infinitely flexible—especially in Excel, one of the most powerful spreadsheet apps. Most people use only a small percentage of their seemingly countless possibilities, however. Yet it doesn't take years of training to take advantage of spreadsheets' power and the automation magic of Excel macros.You likely already use
There question can easily solved with pivot table. For practice i have create the below.
Let us assume that:
- Data appears in Sheet "Data"
Results will be populated in sheet "Results"
Option Explicit Sub Allocation() Dim LastRow As Long, Row As Long, Column As Long, Invetory As Long, Sold As Long, Remaining As Long, LastRowRes As Long, LastColRes As Long, CurrentCol As Long, CurrentRow As Long, i As Long, y As Long Dim iDate As Date Dim Site As String Dim wsData As Worksheet, wsResults As Worksheet Dim ExcistSite As Boolean, ExcistDate As Boolean Set wsData = ThisWorkbook.Worksheets("Data") Set wsResults = ThisWorkbook.Worksheets("Results") LastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row wsResults.UsedRange.Clear For Row = 2 To LastRow iDate = wsData.Cells(Row, 1).Value Site = wsData.Cells(Row, 2).Value Invetory = wsData.Cells(Row, 3).Value Sold = wsData.Cells(Row, 4).Value Remaining = wsData.Cells(Row, 5).Value If Row = 2 Then With wsResults.Range("B1:D1") .Merge .Value = iDate End With wsResults.Range("A2").Value = "Site" wsResults.Range("A2").Offset(1, 0).Value = Site wsResults.Range("B2").Value = "Invetory" wsResults.Range("B2").Offset(1, 0).Value = Invetory wsResults.Range("C2").Value = "Sold" wsResults.Range("C2").Offset(1, 0).Value = Sold wsResults.Range("D2").Value = "Remaining" wsResults.Range("D2").Offset(1, 0).Value = Remaining Else 'Check if Site appears LastRowRes = wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row For i = 3 To LastRowRes ExcistSite = False If wsResults.Cells(i, 1).Value = Site Then CurrentRow = i ExcistSite = True Exit For Else CurrentRow = i + 1 End If Next i If ExcistSite = False Then wsResults.Cells(CurrentRow, 1).Value = Site End If 'Check if date appears LastColRes = wsResults.Cells(1, wsResults.Columns.Count).End(xlToLeft).Column For y = 2 To LastColRes ExcistDate = False If wsResults.Cells(1, y).Value = iDate Then CurrentCol = y ExcistDate = True Exit For Else CurrentCol = y + 1 End If Next y If ExcistDate = False Then wsResults.Cells(2, CurrentCol + 2).Value = "Invetory" wsResults.Cells(i, CurrentCol + 2).Value = Invetory wsResults.Cells(2, CurrentCol + 3).Value = "Sold" wsResults.Cells(i, CurrentCol + 3).Value = Sold wsResults.Cells(2, CurrentCol + 4).Value = "Remaining" wsResults.Cells(i, CurrentCol + 4).Value = Remaining With wsResults.Range(Cells(1, LastColRes + 3), Cells(1, LastColRes + 5)) .Merge .Value = iDate End With Else wsResults.Cells(CurrentRow, CurrentCol).Value = Invetory wsResults.Cells(CurrentRow, CurrentCol + 1).Value = Sold wsResults.Cells(CurrentRow, CurrentCol + 2).Value = Remaining End If End If Next Row LastColRes = wsResults.Cells(1, wsResults.Columns.Count).End(xlToLeft).Column LastRowRes = wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row With wsResults.Range(Cells(1, 2), Cells(1, LastColRes)) With .Font .Bold = True .ThemeColor = xlThemeColorDark1 End With With .Interior .ThemeColor = xlThemeColorAccent1 End With End With With wsResults.Cells(2, 1) With .Font .Bold = True .ThemeColor = xlThemeColorDark1 End With With .Interior .ThemeColor = xlThemeColorLight1 End With End With For i = 2 To LastColRes Step 3 With wsResults.Range(Cells(2, i), Cells(LastRowRes, i)) With .Interior .ThemeColor = xlThemeColorAccent1 .TintAndShade = 0.399975585192419 End With End With Next i For i = 3 To LastColRes + 3 Step 3 With wsResults.Range(Cells(2, i), Cells(LastRowRes, i + 1)) With .Font .ThemeColor = xlThemeColorDark2 .TintAndShade = -0.249977111117893 End With End With Next i With wsResults.UsedRange .Borders.LineStyle = xlContinuous .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .EntireColumn.AutoFit End With End Sub
VBA to Copy and Paste Rows if Condition is Met, I will in this article demonstrate several techniques that extract or filter The following article shows you how to build a formula that uses an arbitrary number of conditions: Extract across all columns in a data set, it also returns all matching records. Now copy cell A20 and paste to cell range A20:E22. I have a VBA code to copy some of the cells in a row to another sheet when a condition is met. The problem is, this only works if the data on sheet one begins in column A but I want my data to start in Column B for aesthetic reasons. Here is the code I have written which only works if my data starts in Column A.
Excel VBA Copy - The Complete Guide to Copying Data, Quite often you build a complex table only to realize that it makes perfect sense to rotate In this article, you will find several ways to convert rows to columns (or columns to rows), Copy the table with "xxx" values, and then use Paste Special > Transpose to flip Transpose data in Excel using VBA macro. I am using currently v lookup to find and place values against the specific item. however, I am looking for help for a VB macro which will out the data in defined outcome. please see 1st screen shot of raw data. second screen shot, should be the outcome. Please note the "site" is not constant it can be any value, so I have put all site in
Extract all rows from a range that meet criteria in one column, In this article, we will create a macro to copy data from a specific column and paste into a new sheet. Raw data for this example consists of employee data in the Hi, I need to copy just one specific cell from the new data sheet, and copy and paste the value into the last row of a specific column on the last day of each month. So copy D7 (on the last day of each month), paste the value below the existing value from the previous month in a specific column (ex: Column D).
How to convert rows to columns in Excel and vice versa, Note that the macro will overwrite any information already on Sheet2. How can I add a condition say If these words are found, I would like to copy the data in the row to Have a script that can go through and identify the unique items in column a and create a new worksheet and move all row data You cannot add another Range in the way you have to a loop. Instead loop the one range as you originally put and as the additional range you want to check matches on a row by row basis but differs in terms of column use OFFSET to test the column I value in the same row. As below: If c = "Yes" And c.Offset(0, 3) = "Yes" So all together: