Ask User what Row to Start Selection At

rcb007

New Member
Joined
Nov 12, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
I have been playing around with this code. I am trying to figure a way once the macro is run, that it prompts the user for a starting row. Then, it completes the rest of the macro.

The macro will allow the user to select a xlsm and copy columns out of the opened worksheet and paste them into the workbook that the command started from.

The designated pasting areas would not change. But sometime the selection starting rows change. Usually, they are at Row 20, but it does change more frequently. As for the ending or how far to copy from the top to the bottom is 180 Rows.

VBA Code:
Sub Import_WB()
Dim WS As Worksheet
Dim wb3 As Workbook, wb4 As Workbook
Dim vFile As Variant

    Application.ScreenUpdating = False

    'Set source workbook
    Set wb3 = ActiveWorkbook

    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls*,", _
                                        1, "Select One File To Open", , False)

    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    
    Set wb4 = Workbooks.Open(vFile)

    'Set selectedworkbook
    Set wb4 = ActiveWorkbook


'''How could I have it prompt the user for what row to start the selection from?

    'Select cells to copy
    wb4.Sheets("Selection_Sheet").Range("D28:M200").Copy

    wb3.Worksheets("Destination_Sheet").Range("D20:M200").PasteSpecial Paste:=xlPasteValues
    
    wb4.Worksheets("Selection_Sheet").Range("X28:Y200").Copy

    wb3.Worksheets("Destination_Sheet").Range("X20:Y200").PasteSpecial Paste:=xlPasteValues
    
    wb4.Worksheets("Selection_Sheet").Range("AE28:AH200").Copy

    wb3.Worksheets("Destination_Sheet").Range("AE20:AH200").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    wb4.Close False

End Sub


I hope this makes sense what i am trying to type and do! Thank you for any pointers and help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
VBA Code:
Sub Import_WB()
Dim WS As Worksheet
Dim wb3 As Workbook, wb4 As Workbook
Dim vFile As Variant

    Application.ScreenUpdating = False

    'Set source workbook
    Set wb3 = ActiveWorkbook

    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls*,", _
                                        1, "Select One File To Open", , False)

    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
   
    Set wb4 = Workbooks.Open(vFile)

'''How could I have it prompt the user for what row to start the selection from?
    Dim rngStart As Range
    wb4.Sheets("Selection_Sheet").Select
    On Error Resume Next
    Set rngStart = Application.InputBox("Please select the top 'Start' cell to copy.", "Start Cell Select", "D20", Type:=8)
    On Error GoTo 0
   
    If Not rngStart Is Nothing Then
        'Select cells to copy
        wb4.Sheets("Selection_Sheet").Range("D" & rngStart.Row).Resize(180, 10).Copy
        wb3.Worksheets("Destination_Sheet").Range("D20").PasteSpecial Paste:=xlPasteValues
       
        wb4.Worksheets("Selection_Sheet").Range("X" & rngStart.Row).Resize(180, 2).Copy
        wb3.Worksheets("Destination_Sheet").Range("X20").PasteSpecial Paste:=xlPasteValues
       
        wb4.Worksheets("Selection_Sheet").Range("AE" & rngStart.Row).Resize(180, 4).Copy
        wb3.Worksheets("Destination_Sheet").Range("AE20").PasteSpecial Paste:=xlPasteValues
   
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End If
   
    wb4.Close False
   
End Sub
 

rcb007

New Member
Joined
Nov 12, 2020
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for your help! I think i understand what you have modified. This will definitely be a keeper for other macros!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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