Set Paste Destination as Last Used Row on Another Sheet

Question

Thanks so much for taking a second to help me a bit! I'm working on a project at the moment, and part of it has me stuck. I'm not terribly proficient with VBA, so its entirely possible that I'm missing something very obvious here.

Goals: Copy a non continuous group of cells (eg. d69,d70,d72,d73,g92,g93, etc.) and paste them to another (This time Continuous) range of cells on another sheet, in the row below the last used row.

Context: Im creating a database of information filled in from a "User Form" on sheet 1. When the user clicks a macro linked button, the data is copied over to sheet 2 as a new entry.

Thoughts: I have been thinking that it may be easier to set a variable to the value of the last cell used in sheet 2, then use something like a range("b" & "aa").pastespecial xlPasteValues for each cell that needs to be copied over. However I cant figure this out, or find what I need to do to achieve this. Any help would be greatly appreciated! Thanks so much.

If you have any questions, or need clarification, let me know! Thanks!

See document link below:

Working File

If i understand correctly, I'd probably put the range of values from sheet1 into an array and then write them to sheet2 from the array. If not resolved I can put some links to helpful resources when on PC but google is your friend. — Samuel Everson, Apr 17 '20 at 23:44

Samuel Everson · Answer 1 · 2020-04-21T16:36:01.703

After your commented clarification and the addition of the Workbook to your question, I've edited this Answer to reflect these updates.

Assuming you know the cell address for each value on sheet1 AND the cell address are constant.

I've written a Subroutine to capture the values of your BBU Quote Entry form and write them to your BBU Quote Database range. I've added this to Module4.

It should be noted, the code only works with the Basic Information section of your form AND the 2 option buttons for Hazardous or Non-Hazardous using a function ReturnFormControlCaption. You can put in the hard yards for the rest of the data (more or less just copy paste, rename variables, adjust the range values and add the variables to the array of course).

Sub BBUEntryToDatabase()

Dim CustCompany As String
Dim CustName As String
Dim CustLocation As String
Dim CMTRep As String
Dim QuoteNo As String
Dim QuoteDate As String
Dim Hazard as String

With ThisWorkbook.Sheets("BBU Quote Entry")

    CustCompany = .Range("D6").Value
    CustName = .Range("D8").Value
    CustLocaction = .Range("D10").Value
    CMTRep = .Range("G6").Value
    QuoteNo = .Range("G8").Value
    QuoteDate = .Range("G10").Value
    Hazard = ReturnFormControlCaption("BBU Quote Entry", "HazardousButton", "NotHazardousButton")

End With

Dim BBUArray As Variant
'The Array is assigned in order of your headings on "BBU Quote Database" sheet
BBUArray = Array(QuoteNo, CustCompany, CustName, CustLocation, CMTRep, QuoteDate, _
 "Clearance", "Height", "Material", "Density", Hazard) 

Dim Destination As Range
Dim LastRow As Long

    With ThisWorkbook.Sheets("BBU Quote Database")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        Set Destination = .Cells(LastRow, 2)
        Set Destination = Destination.Resize(1, UBound(BBUArray, 1) + 1) ' + 1 as the array is 0 based (whereas columns start at 1).
        Destination.Value = BBUArray
    End With

End Sub

Here is a screenshot of my data entry

And the output on "BBU Quote Database" (after 3 tests with the same inputs)

I'm not very familiar with Form Controls as I usually use ActiveX Controls which I find a bit easier to use with VBA - I'd assume there is probably a much cleaner way to deal with the OptionButtons.

The ReturnFormControlCaption() function:

Function ReturnFormControlCaption(ByVal SheetNameTheControlIsOn As String, ByVal FirstFormControlName As String, _
    Optional ByVal SecondFormControlName As String, Optional ByVal ThirdFormControlName As String, _ 
    Optional ByVal FourthFormControlName As String, Optional ByVal FifthFormControlName As String, _ 
    Optional ByVal SixthFormControlName As String) As String

With ThisWorkbook.Sheets(SheetNameTheControlIsOn)

    If SecondFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not Specified"
        End If
    ElseIf ThirdFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    ElseIf FourthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
     ElseIf FifthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FourthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    ElseIf SixthFormControlName = "" Then
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FifthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    Else
        If .Shapes(FirstFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FirstFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SecondFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SecondFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(ThirdFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(ThirdFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FourthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(FifthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(FifthFormControlName).OLEFormat.Object.Caption
        ElseIf .Shapes(SixthFormControlName).OLEFormat.Object.Value = 1 Then
            ReturnFormControlCaption = .Shapes(SixthFormControlName).OLEFormat.Object.Caption
        Else
            ReturnFormControlCaption = "Not specified"
        End If
    End If
End With

End Function

To briefly explain the function, you pass string variables for the relevant worksheet name, and at least one (up to six) form control name(s).

The lengthy and nested If...ElseIf...Else statements are first establishing up to which argument has been included. Then depending on which argument is the first empty or "" value, it executes the next If...ElseIf...Else statement to determine in this case, which OptionButton is selected and then returns the .Caption of that OptionButton.

If none of the OptionButton being evaluated are selected, it returns "Not specified".

Note: This function will work for determining which CheckBox is checked BUT if in your passed arguments, more than one is selected, it will only return the .Caption of the first CheckBox that is checked. With some modification you could get the function working for both types including all CheckBox being checked.

Chip Pearson has some excellent information about Arrays and how to use them. You can read up on them on his website at www.cpearson.com or specifially what we have done here with arrays on this article on his website

Thanks so much for the reply! Sorry for my delayed response, the family and I just moved cross country, so its been a bit crazy around here! I will look at what you suggested. I may need some clarification on a few points, but GoogleU and I will give it a shot first. Thanks so much Samuel! — Jwrigh12, Apr 20 '20 at 17:17
Ok, Ive had a look at some of the information, and looked at your code as well. Forgive my ignorance on this, but... I have several questions. — Jwrigh12, Apr 20 '20 at 17:31
First - in CommandButton1_Click you called out a text box. Possibly I used the wrong terminology. I have them input into a "user form" but really its just a series of cells that I arranged neatly. Not actually a true user form. does that change things? I can send the document if that would help clear things up? — Jwrigh12, Apr 20 '20 at 18:01
Hey no worries. Yes if the data is actually on a `Worksheet` and not a `UserForm` that does change things a bit. It would be better to describe this as something like 'The database is filled based on data input to a range on Sheet1'. The `CommandButton` code is making references to a `MSForms.Userform` object and it's `Controls`. This code was only writing the values to the sheet so if the user writes the values manually, just ignore that part of the code. — Samuel Everson, Apr 20 '20 at 22:26
If you modify the `cmdMoveToSheet2` code (remember this is a `CommandButton` that's been renamed - by default it would be `CommandButton1` too) to suit your sheet names and the correct range/cells and it will work all the same. — Samuel Everson, Apr 20 '20 at 22:30
Awesome! Ill give this a look tomorrow when I get in to work and let you know if I have any additional questions! Thanks so much !@ — Jwrigh12, Apr 21 '20 at 03:14
Compared to your current code the only other think to consider is I've used an ActiveX control whereas you are using form controls (that you assign a macro to) so just chuck the code in one of your modules (create a sub for it) and try it out. I plan on revising some of your procedures to help improve some of your code too, which I can post in a new answer - time permitting. — Samuel Everson, Apr 21 '20 at 03:18
@Jwrigh12 FYI I've updated this answer to reflect new information you've given along with making it relevant to your sheet (after looking over the attached sheet in your question). — Samuel Everson, Apr 21 '20 at 13:37

score 0 · Answer 2 · answered Apr 21 '20 at 15:18

Another way you could achieve this which might be a bit easier is to use a helper column to 'store' your input values and then put that range into an array to write directly to your database sheet.

Assuming your helper columns are on a new sheet named "Helper", data input on a sheet named "BBU Quote Entry" and the data is moving to BBU Quote Database.

Sub BBUEntryToDatabaseUsingHelper()

Dim UserInputsArray() As Variant
Dim HelperRange As Range
Dim Destination As Range
Dim LastBBUDatabaseRow As Long
Dim LastHelperRow As Long

With ThisWorkbook.Sheets("Helper")
    LastHelperRow = .Cells(Rows.Count, 2).End(xlUp).Row
    Set HelperRange = .Range("B2:B" & LastHelperRow)
End With

UserInputsArray() = HelperRange.Value

With ThisWorkbook.Sheets("BBU Quote Database")
    LastBBUDatabaseRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    Set Destination = .Cells(LastBBUDatabaseRow, 2)
    Set Destination = Destination.Resize(1, UBound(UserInputsArray, 1))
    Destination.Value = Application.Transpose(UserInputsArray)
End With

End Sub

Input Form:

Helper columns:

This works simply by referencing the relevant cell from the input sheet on the helper sheet.

E.g. The "Customer Company" value is in cell D6 on sheet BBU Quote Entry so the helper column has ='BBU Quote Entry'!D6

For the "Hazardous" reference I found the cell your Form Control OptionButtons are linked to (E74 on Sheet BBU Quote Entry and used =IF('BBU Quote Entry'!E74 = 1, "Hazardous",IF('BBU Quote Entry'!E74 = 2,"Non-Hazardous","Not Specified"))

As you have some custom formatting, for example the "Desired Clearance" value formats the input as #### Inches, the reference only returns the value entered and not the formatting - You can look further into resolving that but in the mean time I added a string after the value reference, e.g. for "Desired Clearance" =('BBU Quote Entry'!D15) & " Inches".

With the data being held in the correct order for the output onto your "BBU Quote Database" sheet, we can simply put the range from the "Helper" sheet directly into an Array(), and then write the array to the correct range in "BBU Quote Database".

This is what the output looks like:

This is the way I'd probably go about it. Much less code, easier to maintain as both ranges are set dynamically so if you end up adding more inputs to your form, just include their reference on the helper sheet and the code will automatically include the new values the next time you run the code.

You might consider using this approach on your `Equipment Database` sheet to the "Add Entry to Equipment Database" button also - If you're not familiar already, you might find this a good read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10718179/9663006). — Samuel Everson, Apr 21 '20 at 15:22
Wow! Samuel, thanks so much for all the work youve put into this! Cant tell you how helpful this has been. — Jwrigh12, Apr 21 '20 at 16:32
Hopefully it resolves your issue... and hopefully you learn something useful from it! — Samuel Everson, Apr 21 '20 at 16:46
@JWrigh12 It's been a good exersise to get back into programming So thanks! — Samuel Everson, Apr 21 '20 at 16:52
Im working through it now, so I may have a question or two, but Ill keep you updated! Thanks again Samuel — Jwrigh12, Apr 21 '20 at 17:37

Set Paste Destination as Last Used Row on Another Sheet

2 Answers2