Combining multiple separate VBA scripts into one Module

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am at a very beginner level of knowledge, everything I've done in VBA has been self-taught.

I have 3 separate scripts that I need to combine into one script that can used as single button on the control panel tab.

Script 1

VBA Code:
Sub CopyDataFromInQuicker()
   Dim UsdRws As Long
   
   With Worksheets("InquickerData")
      UsdRws = .Range("M:AB").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
      'Copy "MRN" Data
      Worksheets("UploadTemplate").Range("B8").Resize(UsdRws - 1).Value = .Range("BR2:BR" & UsdRws).Value
      'Copy "First Name" Data
      Worksheets("UploadTemplate").Range("D8").Resize(UsdRws - 1).Value = .Range("S2:S" & UsdRws).Value
      'Copy "Last Name" Data
      Worksheets("UploadTemplate").Range("E8").Resize(UsdRws - 1).Value = .Range("U2:U" & UsdRws).Value
      'Copy "Middle Initial" Data
      Worksheets("UploadTemplate").Range("F8").Resize(UsdRws - 1).Value = .Range("T2:T" & UsdRws).Value
      'Copy "Birthdate" Data
      Worksheets("UploadTemplate").Range("G8").Resize(UsdRws - 1).Value = .Range("AA2:AA" & UsdRws).Value
      'Copy "Email" Data
      Worksheets("UploadTemplate").Range("H8").Resize(UsdRws - 1).Value = .Range("Y2:Y" & UsdRws).Value
      'Copy "City" Data
      Worksheets("UploadTemplate").Range("J8").Resize(UsdRws - 1).Value = .Range("V2:V" & UsdRws).Value
      'Copy "State" Data
      Worksheets("UploadTemplate").Range("K8").Resize(UsdRws - 1).Value = .Range("V2:V" & UsdRws).Value
      'Copy "Zip" Data
      Worksheets("UploadTemplate").Range("L8").Resize(UsdRws - 1).Value = .Range("X2:X" & UsdRws).Value
      'Copy "Gender" Data
      Worksheets("UploadTemplate").Range("P8").Resize(UsdRws - 1).Value = .Range("AB2:AB" & UsdRws).Value
      'Copy "Registration Date" Data
      Worksheets("UploadTemplate").Range("U8").Resize(UsdRws - 1).Value = .Range("M2:M" & UsdRws).Value
   End With
End Sub

Script 2 part 1 = I have a field that the end user will manually enter in on a Command Tab. Then the below script will copy and paste the the entered fields into their respective columns on another page.

VBA Code:
Sub Copy_Paste()
'Copy "Lead Source" Data
  Worksheets("Control Panel").Range("X8").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("Q8").PasteSpecial Paste:=xlPasteValues

'Copy "MAC ID" Data
  Worksheets("Control Panel").Range("X10").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("S8").PasteSpecial Paste:=xlPasteValues
  
  'Copy "Status" Data
  Worksheets("Control Panel").Range("X12").Copy

'PasteSpecial Values Only
  Worksheets("UploadTemplate").Range("T8").PasteSpecial Paste:=xlPasteValues

'Autofill To end of data


'Clear Clipboard (removes data around your original data set)
  Application.CutCopyMode = False
  
End Sub

Script 2 Part 2=This script is intended to fill down the each of the newly created fields to the end of the data from "First Name" Data from the "UploadTemplate" Tab.

**Please note that this script works individually but it fills down 4 rows past the last entered row of text in the "First Name" Column (Please see screenshot for reference)

VBA Code:
'Autofill To end of data
Sub Copy_Paste1()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Range("Q8").AutoFill Destination:=Range("Q8:Q" & LR)
Range("S8").AutoFill Destination:=Range("S8:S" & LR)
Range("T8").AutoFill Destination:=Range("T8:T" & LR)
End Sub


If someone can please help that would be greatly appreciated.
 

Attachments

  • Script 2 Part 1 - Issue.png
    Script 2 Part 1 - Issue.png
    71.1 KB · Views: 4

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Three Procedures in One

VBA Code:
Option Explicit

Sub copyData()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("UploadTemplate")
    
    Dim sLast As Long
    Dim tLast As Long
    
    ' Copy from InQuicker
    With Worksheets("InquickerData")
        sLast = .Range("M:AB") _
                .Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
        tLast = sLast - 1
        ' Copy "MRN" Data
        tgt.Range("B8").Resize(tLast).Value = .Range("BR2:BR" & sLast).Value
        ' Copy "First Name" Data
        tgt.Range("D8").Resize(tLast).Value = .Range("S2:S" & sLast).Value
        ' Copy "Last Name" Data
        tgt.Range("E8").Resize(tLast).Value = .Range("U2:U" & sLast).Value
        ' Copy "Middle Initial" Data
        tgt.Range("F8").Resize(tLast).Value = .Range("T2:T" & sLast).Value
        ' Copy "Birthdate" Data
        tgt.Range("G8").Resize(tLast).Value = .Range("AA2:AA" & sLast).Value
        ' Copy "Email" Data
        tgt.Range("H8").Resize(tLast).Value = .Range("Y2:Y" & sLast).Value
        ' Copy "City" Data
        tgt.Range("J8").Resize(tLast).Value = .Range("V2:V" & sLast).Value
        ' Copy "State" Data
        tgt.Range("K8").Resize(tLast).Value = .Range("V2:V" & sLast).Value
        ' Copy "Zip" Data
        tgt.Range("L8").Resize(tLast).Value = .Range("X2:X" & sLast).Value
        ' Copy "Gender" Data
        tgt.Range("P8").Resize(tLast).Value = .Range("AB2:AB" & sLast).Value
        ' Copy "Registration Date" Data
        tgt.Range("U8").Resize(tLast).Value = .Range("M2:M" & sLast).Value
    End With
    
    ' Copy from Control Panel.
    With wb.Worksheets("Control Panel")
        ' Copy "Lead Source" Data
        tgt.Range("Q8").Value = .Range("X8").Value
        ' Value "MAC ID" Data
        tgt.Range("S8").Value = .Range("X10").Value
        ' Value "Status" Data
        tgt.Range("T8").Value = .Range("X12").Value
    End With
    
    ' Copy in ActiveSheet.
    With ActiveSheet
        tLast = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("Q8").AutoFill Destination:=.Range("Q8:Q" & tLast)
        .Range("S8").AutoFill Destination:=.Range("S8:S" & tLast)
        .Range("T8").AutoFill Destination:=.Range("T8:T" & tLast)
    End With

End Sub
 
Solution

J7House1984

New Member
Joined
Oct 30, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Three Procedures in One

VBA Code:
Option Explicit

Sub copyData()
  
    Dim wb As Workbook
    Set wb = ThisWorkbook
  
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("UploadTemplate")
  
    Dim sLast As Long
    Dim tLast As Long
  
    ' Copy from InQuicker
    With Worksheets("InquickerData")
        sLast = .Range("M:AB") _
                .Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
        tLast = sLast - 1
        ' Copy "MRN" Data
        tgt.Range("B8").Resize(tLast).Value = .Range("BR2:BR" & sLast).Value
        ' Copy "First Name" Data
        tgt.Range("D8").Resize(tLast).Value = .Range("S2:S" & sLast).Value
        ' Copy "Last Name" Data
        tgt.Range("E8").Resize(tLast).Value = .Range("U2:U" & sLast).Value
        ' Copy "Middle Initial" Data
        tgt.Range("F8").Resize(tLast).Value = .Range("T2:T" & sLast).Value
        ' Copy "Birthdate" Data
        tgt.Range("G8").Resize(tLast).Value = .Range("AA2:AA" & sLast).Value
        ' Copy "Email" Data
        tgt.Range("H8").Resize(tLast).Value = .Range("Y2:Y" & sLast).Value
        ' Copy "City" Data
        tgt.Range("J8").Resize(tLast).Value = .Range("V2:V" & sLast).Value
        ' Copy "State" Data
        tgt.Range("K8").Resize(tLast).Value = .Range("V2:V" & sLast).Value
        ' Copy "Zip" Data
        tgt.Range("L8").Resize(tLast).Value = .Range("X2:X" & sLast).Value
        ' Copy "Gender" Data
        tgt.Range("P8").Resize(tLast).Value = .Range("AB2:AB" & sLast).Value
        ' Copy "Registration Date" Data
        tgt.Range("U8").Resize(tLast).Value = .Range("M2:M" & sLast).Value
    End With
  
    ' Copy from Control Panel.
    With wb.Worksheets("Control Panel")
        ' Copy "Lead Source" Data
        tgt.Range("Q8").Value = .Range("X8").Value
        ' Value "MAC ID" Data
        tgt.Range("S8").Value = .Range("X10").Value
        ' Value "Status" Data
        tgt.Range("T8").Value = .Range("X12").Value
    End With
  
    ' Copy in ActiveSheet.
    With ActiveSheet
        tLast = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("Q8").AutoFill Destination:=.Range("Q8:Q" & tLast)
        .Range("S8").AutoFill Destination:=.Range("S8:S" & tLast)
        .Range("T8").AutoFill Destination:=.Range("T8:T" & tLast)
    End With

End Sub
ok, Awesome. I will test this out and reach out if I have any issues. Thanks so much.


***Update*** This worked perfectly. Thank you so very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,955
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top