Posts by DDH

Re: Copy InputBox

Thank you for your help.
This is what I am trying to make the coe do.
I want to insert a blank row and I will put the curser at Column A an the Row above where I want the line inserted (just like the normal built in Excel insert).
But I want to insert the line from Column A to Column Q only and I want to be able to tell it how many lines I want inserted.
The code below works now but I have another problem.
If I open the Inputbox and decide I don't want to do it and
If I click on the cancel button of the InputBox it will error out.
I want it to just stop if I don't enter a number.
Thank you for your help.

Sub InsertRowA601TA()
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
Dim Rng
Dim i As Long, RowIns As Long


RowIns = InputBox("Enter number of rows required")
If RowIns = "" Then
            [C3].Select
            Exit Sub
        End If






For i = 1 To RowIns
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Cells(ActiveCell.Row, 1).Resize(1, 2).Copy Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i






Cells(ActiveCell.Row + 1, 1).Select
Set Rg = Nothing
ActiveCell.Offset(0, 2).Select




End Sub

I am trying to enter a number in an InputBox and it will copy down the previous line the number that I put in. This code works for 1 line but not for 2 or 3 lines.
Thank you for your help.

Sub PasteRowAX1T()
' PasteRowAX1 Macro
Application.ScreenUpdating = False


Dim Rg As Range
Dim SRg As String
Dim LRg As String


RowIns = InputBox("Enter number of rows required")
For i = 1 To RowIns




Set Rg = Selection
SRg = Range(Rg.Item(0), Rg.Item(i).Offset(0, 16)).Address
Range(SRg).Select
LRg = Range(Rg.Item(0).Offset(1, 16), Rg.Item(i).Offset(0, 16)).Address
Selection.Copy Destination:=Range(SRg & ":" & LRg)


Cells(ActiveCell.Row, 1).Resize(1, 17).Copy Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)


Next i


Cells(ActiveCell.Row + 2, 1).Select
Set Rg = Nothing


Application.ScreenUpdating = True


End Sub

I was trying to see if there was code that could be applied to a button and instead of the whole row being selected that just certain cells could be transfered over.
I am sorry that I did not say something about the previous post. Please accept my apology, it was not my intent to offend anyone.

I have a macro on each line of a daily posting sheet that when clicked it copies the cells contents of an account number and then brings up the GOTO box and pastes in the copied account number (contents will be a cell name like A789A) and then the GOTO will take me to that cell name on another a Ledger sheet and the data that was entered on the posting sheet will be drawn over to the Ledger sheet.(the destination cell will have that name)
The problem is that I don't want the cell contents to hardwired into the VBA code. The macro will be the same for each line, the only thing different will be where it goes to put the data that was entered.

Is this possible?

Thank you very much for your help.";
[email protected]

I found this code on your site and I can not get it to work, I know that I am doing something wrong,I just don't know what and I hope someone can help me.
I have pasted the code into a new module and change the cell locations and clicked on the cells and nothing happens.
Thank you for your help.

[email protected]

Following procedure insert an X in the activecell when doubleclicking on the cell.

This might be useful when we use a sheet as a form for filling in different options and so on.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rnData As Range

Set rnData = Range("E17:E20")

If Not Intersect(Target, rnData) Is Nothing Then ActiveCell.Value = "X"

Cancel = True

End Sub

Questions:
1. How can I make it paste to the disc? (The ActiveSheet.Paste I can not get it to work)

2. How can I save it to any file name (One file) on the disc, Not have the "County" name in the code.

Thank you very much for all of your help.

I. The office puts 5 columns of data on to an Excel sheet contained on a disc. The disc has a Start macro on it and he clicks on it and it loads the 5 columns on to the HD.

II. The Excel file and disc is given a unique name .
(The disc and its file on the disc will have the same name, one file per disc)
(Each Disc will have a different name)

III. The disc is given to a field person and he loads ("Start" Macro) the disc data
on to his laptop HD which has a mirror image of the disc Excel sheet
with a master program in it to calculate the data that he enters on to the Excel Sheet.

iV. When all of the data has been entered on the Excel sheet on his laptop HD with the previous 5 columns of data and
and the new 10 columns of calculated data.

V. He clicks a "Save" macro which takes the completed Excel sheet and transfers it to the disc.
Thank you so very much for all of your help.

This is a small sample part of the "SAVE" macro code. This macro is located on the HD in the master program and is not loaded with the Start macro from the disc.

Range("A1:C1").Select
Selection.Copy

Dim County As String
County = Dir("A:\*.xls")
Workbooks.Open "A:\" & County

Range("A1:C1").Select
ActiveSheet.Paste