Posts by Bob Chiskey

Dear Forum,

I have the below 2 sets of code and I am trying to combine them into 1 (as a newbie not sure if this is doable or if there is a better way to do this?). The outcome should be that:

A new sheet is created when the Transfer Sub runs, and;
The formatting and forumlas from the ws named "Template" are copied to the new sheet and any additional new sheets that are created when the FormatNewSheet sub runs.
Please could yo help me merge the 2 or suggest a better way?

Public Sub transfer()


Dim ws As Worksheet, wsName As Worksheet
Dim lRow As Long, lPaste As Long
Dim sName As String


Set ws = Worksheets("Update Quality Check Data")


With ws
    For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        sName = .Cells(lRow, 2)
        On Error GoTo NoSheettFound
Jumper:
        Set wsName = Worksheets(sName)
        On Error GoTo 0
        lPaste = wsName.Cells(Rows.Count, 3).End(xlUp).Row + 1
        .Cells(lRow, 1).Copy Destination:=wsName.Cells(lPaste, 3)
        .Cells(lRow, 3).Copy Destination:=wsName.Cells(lPaste, 4)
    Next lRow
End With


Exit Sub


NoSheettFound:
Set wsName = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsName.Name = sName
ws.Select
GoTo Jumper
End Sub

Sub FormatNewSheet(ws As Worksheet)
Dim wsTemplate As Worksheet
Set wsTemplate = Worksheets("Bob")
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.CutCopyMode = False
'Copy the range from the template
wsTemplate.Range("D5:G10").Copy


'Paste the format to the new range
ws.Select
ws.Range("D5:G10").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.EnableEvents = True
Application.CutCopyMode = xlCopy
Application.ScreenUpdating = True


End Sub

Kind Regards
Dave

Re: VBA Copy Paste to each new worksheet with formatting and formulas

Could someone please advise if this might be the correct fix to my question?, I only need to know how to change "Bob" in the code below so that the formmating would copy to each new sheet with a different name. Does VBA work with wild cards like "*" for example?:

range("B2:B5002").Copy Destination:=Sheets("Bob").Cells(startrow, 2)

Dear Forum,

I have the below code running in my project that transfers data from a worksheet called "Update Quality Check Data" to other weeksheets based on user names by 1 of 2 ways, either:

• By seeing the user name of the worksheet already exists and just copying the relevant data; or,
• By creating a new worksheet with the user name as the ws name and copying the data from the data shee

What I would like to add would be when a new user sheet is created the format and forumlas from the first usersheet are copied into the new sheets and each additional user sheet that is created.

I have seen many threads to copy paste and the arguements between clipboard and pastespecial but now I am rather confused and not sure how to do this for sheets that do not currently exist. Could some please help me?

I have attached a sample sheet, so I would just want the formulas and borders copied from the worksheet called Bob forum.ozgrid.com/index.php?attachment/69687/to each new sheet that is created.

Public Sub transfer()
     
     
    Dim ws As Worksheet, wsName As Worksheet
    Dim lRow As Long, lPaste As Long
    Dim sName As String
     
     
    Set ws = Worksheets("Update Quality Check Data")
     
     
    With ws
        For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            sName = .Cells(lRow, 2)
            On Error GoTo NoSheettFound
Jumper:
            Set wsName = Worksheets(sName)
            On Error GoTo 0
            lPaste = wsName.Cells(Rows.Count, 3).End(xlUp).Row + 1
            .Cells(lRow, 1).Copy Destination:=wsName.Cells(lPaste, 3)
            .Cells(lRow, 3).Copy Destination:=wsName.Cells(lPaste, 4)
        Next lRow
    End With
     
     
    Exit Sub
     
     
NoSheettFound:
    Set wsName = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsName.Name = sName
    ws.Select
    GoTo Jumper
End Sub

Re: VBA Transfer Macro

Thanks Ger, you are the man working as I needed, many thanks!

Re: VBA Transfer Macro

Hi Ger,

Can you advise me please? I'm not so great with VBA (yet). I already have the below code for the Submit Buttom (CommandButton1), can you advise me where / how I add the transfer procedure?

Private Sub CommandButton1_Click()


Dim emptyRow As Long


'Make Sheet2 active
 Sheet2.Activate


'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
 Cells(emptyRow, 1).Value = TextBox8.Value
 Cells(emptyRow, 2).Value = AgentNameBox.Value
 Cells(emptyRow, 3).Value = TicketNumberTextBox.Value
 Cells(emptyRow, 4).Value = TextBox9.Value
 Cells(emptyRow, 5).Value = NameComboBox.Value
 Cells(emptyRow, 6).Value = TextBox2.Value
 Cells(emptyRow, 7).Value = PhoneComboBox.Value
 Cells(emptyRow, 8).Value = TextBox3.Value
 Cells(emptyRow, 9).Value = StatusComboBox.Value
 Cells(emptyRow, 10).Value = TextBox4.Value
 Cells(emptyRow, 11).Value = NextStepsComboBox.Value
 Cells(emptyRow, 12).Value = TextBox5.Value
 Cells(emptyRow, 13).Value = CBTComboBox.Value
 Cells(emptyRow, 14).Value = TextBox6.Value
 Cells(emptyRow, 15).Value = SpellingComboBox.Value
 Cells(emptyRow, 16).Value = TextBox7.Value


End Sub

Dear Forum,

I currently have the below code in my workbook that transfers data from a data sheet into individual employee sheets. This works fine so far but I would like to ask if anyone could please assist me in a few adjustments?

What I am trying to achieve with the amendments would be:

1. Is it possible when the user hits the Submit button on the UserForm1 that the sheets automatically update? reason I ask is that now it works great but I need to run the code each time new data is added, and this then means duplicate entries, which would also lead me to ask;
2. if 1. is possible is there a way to avoid duplicate entries? however it is possible the same ticket number a. could appear for more than 1 user and b. could appear more than once for the same user

Sub transfer()
    
    
    Dim ws As Worksheet, wsName As Worksheet
    Dim lRow As Long, lPaste As Long
    Dim sName As String
    
    
    Set ws = Worksheets("Update Quality Check Data")
    
    
    With ws
        For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            sName = .Cells(lRow, 2)
            On Error GoTo NoSheettFound
Jumper:
            Set wsName = Worksheets(sName)
            On Error GoTo 0
            lPaste = wsName.Cells(Rows.Count, 3).End(xlUp).Row + 1
            .Cells(lRow, 1).Copy Destination:=wsName.Cells(lPaste, 3)
            .Cells(lRow, 3).Copy Destination:=wsName.Cells(lPaste, 4)
        Next lRow
    End With
    
    
    Exit Sub
    
    
NoSheettFound:
    Set wsName = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsName.Name = sName
    ws.Select
    GoTo Jumper
End Sub

Kind Regards

Bob

Re: VBA Find Copy Paste

Thanks Fuzz-Head you are a legend it works like a charm! I would just like to ask if the following would be possible as an adjustment?

1. Is it possible when the user hits the Submit button on the UserForm1 that the sheets automatically update? reason I ask is that now it works great but I need to run the code each time data is added, and this then means duplicate entries, which would also lead me to ask
2. if 1. is possible is there a way to avoid duplicate entries? it is possible the same ticket number a. could appear for more than 1 user and b. could appear more than once for the same user

Can the code be appended to the Submit button to achieve this?

Kind Regards

Bob

Re: VBA Find Copy Paste

I have added the attachment to help explain.

So I need the Macro to look at A:A and B:B in ws Update Quality Check Data. Based on the results copy the ticket numbers into the table on ws based on the user name

Hope that makes sense

Bob

Dear Forum,

Newbie to VBA here and would appreciate some advice or help on the below query please.

I have searched through the forum and Google and found a few bits and pieces that almost match but I dont have the knowhow to amend the code. What I am trying to achieve is:

- I have a basic userform to enter data, when a command button is clicked the data is transferred into a ws called "Update Quality Check Data" - this works perfeclty so far
- What I am now trying to do is find data in the ws "Update Quality Check Data: transfer it to a table in another sheet.

In the ws:

- Column A:A contains Months
- Column B:B contains Names
- Column C:C contains Ticket Numbers

What I need to do is transfer onlythe Ticket numbers to seperate sheets If month and Name match, the sheet looks something like this:

A B C
June John 123
June William 456
July John 789
July David 101
July Bob 112

So to summarize what I would like to achieve is:

IF A:A = "June", AND B:B = "John"
Copy C:C orders of John in June, July etc. to ws named John starting at cell D5 and going down until there is no more matching entries

I have tried to have a go as per below but failed miserably so far:

Private Sub Data_Transfer()


 Dim LastRow As Long
 'Ticket is the data on Update Quality Checks Data sheet to be transferred to Sheet "David"
 Dim Ticket As Range


 LastRow = Cells(Row.Count, "C").End(xlUp).Row


 'Activate Quality Checks sheet
 Sheets("Update Quality Checks Data").Select
 'Select the Range to be copied
 Range("C2:C" & LastRow).Select




    For Each TicketRow In Range("C7:C" & LastRow)
 'If A:A on Update Quality Sheet Data = June and B:B = John, copy C:C to ws John starting from cell D5


         If Range("A:A").Value = "June" And Range("B:B").Value = "John" Then
            Selection.Cut
            Sheets("David").Select
            Range("D5" & Columns.Count).End(xlUp).Offset(1).Select
            ActiveSheet.Paste


        End If
    Next Ticket


 End Sub

Is this possible? and as people enter the data into the useform would this automatically update the table on John's ws?

Sorry for the complex babble, hopefully it makes some sense.

Kind Regards

Bob