Combining multiple separate VBA scripts into one Module

J

J7House1984

New Member
Joined
Oct 30, 2020
Messages
21
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: 25

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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
 
Upvote 0
Solution
VBasic2008 said:
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
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Set a network path in VBA for excel formulas
Replies
5
Views
452
ItalianPlatinum
I
K
  • Question
VBA Code remove duplicates not working
Replies
1
Views
730
Kra
K
B
  • Solved
VBA
Replies
5
Views
96
igold
igold
T
  • Question
Copy row values from one worksheet in one workbook to multiple workbooks based on file name
Replies
0
Views
152
thechad
T
tabbytomo
  • Solved
Copy entire row if cell has specific value, looking at multiple columns and worksheets.
Replies
2
Views
280
NateSC
NateSC

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

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
Back
Top