I need to select data from one Excel worksheet and copy them to another worksheet, however I need to perform the following data copy operations:
For each row in the source sheet, select cells by column (which I can predefine, possibly using an array or something else).
Manipulate data to change the orientation on a new sheet. See screenshot below.
It’s hard for me to explain exactly what I mean, so I hope that my screenshot will tell me what I need.
For each row there is a channel value, I need to order and condense all the results on the channel. There is also a need to check the results with a constraint, but I can cross them after solving this problem.
I have my code below, I appreciate that there may be errors, as this is my first script. Do not pay attention to the data on the channels that I still struggle with, even to select the necessary columns and copy them to a new worksheet.
The first part of the code is checking and creating a new worksheet. After that, he moves on to defining the variables and arrays that I can predefine the columns that I want. It ends with a loop that checks the number of rows (although I want it to repeat as many rows as there are), and inside that there is another loop for each row, captures a cell based on my predefined columns.
When debugging, it is displayed as an error of an object or application in the copy sheet function right below inside the loops. I am not sure where I am going wrong. Where am I mistaken and is there a better way to attack this?
Sub Process_Results() 'User defines the worksheets for this script sourcedatasheet_name = InputBox("Enter the customer data sheet name: ", "Enter Worksheet Name") For rep = 1 To (Worksheets.Count) If LCase(Sheets(rep).Name) = LCase(sourcedatasheet_name) Then Exit For ElseIf (rep = Worksheets.Count) And (LCase(Sheets(rep).Name) <> LCase(sourcedatasheet_name)) Then MsgBox "This sheet does not exist!" Exit Sub End If Next destinationdatasheet_name = InputBox("Enter the destination worksheet name to write the data to: ", "Enter Destination Worksheet Name") For rep = 1 To (Worksheets.Count) If LCase(Sheets(rep).Name) = LCase(destinationdatasheet_name) Then MsgBox "This sheet already exists!" Exit Sub End If Next Sheets.Add After:=Sheets(Sheets.Count) Sheets(ActiveSheet.Name).Name = destinationdatasheet_name 'These are the variables for referencing data sets in the source sheet Dim source_testmodel Dim source_testcasename Dim source_measurementname Dim source_carrierfrequency Dim source_limitlow Dim source_limithigh Dim source_measuredresult Dim source_measurementunit 'These are the variables for referencing data set columns in the processed data sheet Dim destination_testmodel Dim destination_testcasename Dim destination_measurementname Dim destination_carrierfrequency_bottomchannel Dim destination_carrierfrequency_middlechannel Dim destination_carrierfrequency_topchannel Dim destination_measuredresult 'Define the column number and cell column reference for each data set that will be used to retrieve information from the source sheet source_testmodel = 9 source_testname = 11 source_measurementname = 12 source_measuredcarrierfrequency = 13 source_measurementlimitlow = 15 source_measurementlimithigh = 16 source_measuredresult = 17 source_measurementunit = 18 Dim array_source_fields(8) As Variant array_source_fields(1) = source_testmodel array_source_fields(2) = source_testname array_source_fields(3) = source_measurementname array_source_fields(4) = source_measuredcarrierfrequency array_source_fields(5) = source_measurementlimitlow array_source_fields(6) = source_measurementlimithigh array_source_fields(7) = source_measuredresult array_source_fields(8) = source_measurementunit 'Define the column number and cell column reference for each data set that will be used to write information to the processing sheet destination_testmodel = 1 destination_testname = 2 destination_measurementname = 3 destination_channelbottom = 4 destination_channelmiddle = 5 destination_channeltop = 6 Dim array_processed_fields(6) As Variant array_processed_fields(1) = destination_testmodel array_processed_fields(2) = destination_testname array_processed_fields(3) = destination_measurementname array_processed_fields(4) = destination_channelbottom array_processed_fields(5) = destination_channelmiddle array_processed_fields(6) = destination_channeltop 'Start processing data Dim y As Variant Dim lastrow As Long For x = 1 To 100 'row 'lastrow=activesheet.usedrange.specialcells(xlCellTypeLastCell) For Each y In array_source_fields 'y = LBound(Application.Transpose(array_source_fields)) To UBound(Application.Transpose(array_source_fields)) Sheets(sourcedatasheet_name).Cells(x, y).Copy Destination:=Sheets(destinationdatasheet_name).Cells(x, y) Next y Next x End Sub
source share