• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

How can I reduce my copying/pasting/typing and sorting in these 3 sheet tabs?

I currently copy and paste everything from my first tab to the other 2 tabs and then I have to sort and remove colors/lines, and center and bold everything.  This information is used to track our products manufactured.  I am looking for an easier way to do this with a macro.   Is this possible with a maro? Trial.xlsx
0
topgun0621
Asked:
topgun0621
  • 4
  • 3
  • 2
1 Solution
 
andymacfCommented:
You can record your own macro to do this, click the 'Home' button, followed by 'Excel Options' in order to see the developer tab, then you can start recording your own macro.

Hope this helps
Andy
0
 
andymacfCommented:
You just have to start the recording and carry out the tasks that you carry out and when you have finished stop the recording and try it out again.

Andy
0
 
topgun0621Author Commented:
so i do not need to write a macro, just record it?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andymacfCommented:
As long as you are confident of the order you wish to carry out the task, you can record it, assign a letter to it and once saved you can run it by using CTRL + (key allocated)

Andy
0
 
dlmilleCommented:
Recording a macro is not going to help, based on the circumstances you're dealing with - the specifics of what you're doing need a bit more intelligence...


Is this something like you're trying to do?

How about - select any cell on the row you just typed in in the "to be Manufactured" tab.  Then, hit a button to add to your "Description database".

The macro checks to see if the combination of stock, description and weight exists (no case sensitivity) in the "Description database" tab.  If not, then its added to the bottom.  Thus, there should be no duplicates.

The Data Filter on the "Description database" can be configured to "sort smallest to largest" so the macro can then reapply that filter.

Following a similar approach, the app then adds the same line to the "Certification Results" tab, and applies the active filter that already has a sorting setup.

Check this out and see if that's where you're headed.

Code:
Option Explicit
Sub addToDescriptionDatabase()
Dim inputSheet As Worksheet
Dim outputSheet As Worksheet
Dim outputsheet2 As Worksheet
Dim mySelection As Range

Dim mycell As Range, found As Boolean
Dim checkString1 As String, checkstring2 As String
Dim lastRow As Long

    Set inputSheet = ActiveSheet
    Set outputSheet = Sheets("Description database")
    Set mySelection = inputSheet.Range(ActiveCell.Address)
    
    lastRow = outputSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    checkString1 = UCase(Cells(mySelection.Row, 2).Value) & UCase(Cells(mySelection.Row, 3).Value) & UCase(Cells(mySelection.Row, 4).Value)
    
    found = False
    For Each mycell In outputSheet.Range("A9", outputSheet.Range("A" & Rows.Count).End(xlUp))
        
        checkstring2 = UCase(mycell.Value) & UCase(mycell.Offset(0, 1).Value) & UCase(mycell.Offset(0, 2).Value)
        
        If checkstring2 = checkString1 Then found = True
        
    Next mycell
    
    If Not found Then
        outputSheet.Cells(lastRow, 1).Resize(1, 3).Value = Cells(mySelection.Row, 2).Resize(1, 3).Value
        outputSheet.AutoFilter.ApplyFilter
    End If
    
    found = False
    
    Set outputSheet = Sheets("Certification Results")
    
    'duplicates ok in this sheet, as this appears to be a set of batches, so nothing to check
    
    lastRow = outputSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    outputSheet.Cells(lastRow + 1, 1).Resize(1, 4).Value = Cells(mySelection.Row, 1).Resize(1, 4).Value
    outputSheet.Cells(lastRow, 1).EntireRow.Copy
    outputSheet.Cells(lastRow + 1, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    outputSheet.AutoFilter.ApplyFilter

    
End Sub

Open in new window


See attached file and try it out!

Enjoy!

Dave
Trial-r1.xlsm
0
 
topgun0621Author Commented:
dlmille,

Wow, exactly what I was trying to accomplish.  I was trying the record macro feature,but was running into issues.  That works fine what you did, but I do need to add information and sort all the time in the certification sheet tab.  This won't mess the code up will it?  

In other words the information is typed into the "To be manufactured" tab but sometimes it is a day or later before we get the testing results and are able to add the heat number information.  So as we get the information we will scroll or look up the weight we are intending to completely certify.  This was just a sample file, the current file is database big.  Actually, the certifieds is in its own spreadsheet and we seperated the wire sizes into sheet tabs like W2,W2.5,W3 ect.. all the way to W31.  I think it is better to just put into one tab as i have done and in the same spreadsheet as the 'To be manufactured" and description.
0
 
topgun0621Author Commented:
I forgot, is there a macro for date?  We currently type that in everytime, but if it can be added everytime we perform the maco in the certified tab that would be awesome.
0
 
dlmilleCommented:
You can add data to the certification sheet, no problem.  It won't mess up the code, as adding a record from the to manufacture tab just adds to the botto and applies whatever data filter sorts you have active.

To add date to the certified tab, just use the attached...

If you have related problems adding to your production spreadsheet, just post here.  If its a future problem, post here too and I'll be alerted...

Dave
Trial-r1.xlsm
0
 
topgun0621Author Commented:
very well put together. Iappreciate the time you took to make this work, thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now