'Using user entry to copy paste range of cells
I am working on a small task where I need to extract selective data from one workbook to another, and plot the graphs. Now, I have the following code which copies the data from source sheet and pastes into the destination sheet. But the issue is, the selection of data will be different each time i.e., it may be A2:A9 and D2:D9 this time, but next time it could be A2:A100 and D2:D100. Is there anyway I can setup a control where a user can input the beginning and end range of each column to select the data?
Many thanks in advance.
Private Sub CommandButton1_Click()
Workbooks("PressureTest.xlsx").Worksheets(1).Range("A2:A9,D2:D9").Copy _
ThisWorkbook.Worksheets("Sheet1").Range("A1")
Columns("A:A").Select
Selection.NumberFormat = "0"
Columns.AutoFit
End Sub
Solution 1:[1]
This should get you started.
LastRow:
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastColumn:
Dim sht As Worksheet
Dim LastColumn As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Using UsedRange
sht.UsedRange 'Refresh UsedRange
LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
All details are available here.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | ASH |