Hello,
I have one macro that is copy rows from a master sheet to another sheet depending on cell values from column D.
There is a link between the cell value (in my macro is now 10001) and sheet name (St1).
The values could variate from 10001/St1 .... 10010/St10.
My questions are:
- How could I have a message at beginning of macro to select/input a specific value (1...10) and avoid to change macro all time (by replacing 10001 with 10007 for example)?
- How could I have a message at beginning of macro to select/input to copy all data (1...10) and not only a single value (for ex. 10005)?
Thank you for your time.
I have one macro that is copy rows from a master sheet to another sheet depending on cell values from column D.
There is a link between the cell value (in my macro is now 10001) and sheet name (St1).
The values could variate from 10001/St1 .... 10010/St10.
My questions are:
- How could I have a message at beginning of macro to select/input a specific value (1...10) and avoid to change macro all time (by replacing 10001 with 10007 for example)?
- How could I have a message at beginning of macro to select/input to copy all data (1...10) and not only a single value (for ex. 10005)?
Code:
Sub Copy1ST()
'Select older extracted data a deletes them; to modfy size if anything changes
Sheets("St1").Activate
Range("B2:XFD2000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Set i = Sheets("Data")
Set e = Sheets("St1")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("D" & j))
If i.Range("D" & j) = "10001" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
'delete first column data not interesting for us
Sheets("St1").Select
Range("A2:A2000").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
MsgBox "Done!!!"
End Sub
Thank you for your time.