What governs cells to copy in this macro, pls?

[StargateFanFromWork]

Hello! I have this great macro provided to me by a kind poster over a year
ago. I've never been able to figure out how to make what is copied a range.
I believe the "Range("A" & j & "").Select copies the first cell of column A
of the row that the cursor is on, which is what was needed at the time.
Today's sheet has formulas and/or formatting in most of the cells in the
row, so just easier to copy the row in the print area. Is it possible to
specify a set of cells that are then copied before macro pastes to a new
row? I currently need a modified macro using this code that copies from
column A to K of the empty row in A2 the cursors lands on in the beginning,
to new location user specifies. Here is the existing code:

**************************************
Sub InsertROWS()
'
ActiveSheet.Unprotect 'place at the beginning of the code

Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim i As Long
Dim j As Long
Dim k As Long

On Error GoTo dontdothat
Do
i = InputBox("How many rows do you want to insert?", "Insert Rows ",
1)
Loop Until i <> 0
Do
j = InputBox("At what Excel row number do you want to start the
insertion?", "Insert Rows", 10)
Loop Until j <> 0

k = j + i - 1
Range("" & j & ":" & k & "").Insert shift:=xlDown
j = j - 1
Range("A" & j & "").Select
Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""),
Type:=xlFillDefault
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Protect ' place at end of code
Exit Sub

dontdothat:
ActiveSheet.Protect ' place at end of code
End Sub
**************************************

I hope the question is clear ... (?) Thanks for any help. D

 

[StargateFan]

Hello! I have this great macro provided to me by a kind poster over a
year ago. I can't figure out how to change the range of what is
copied in a row in it.

Is it possible to specify a set of cells that are then copied before
macro pastes to a new row? What is needed is to have a modified
version of this macro copy from row landed on by macro from A to K of
that empty row to the new location user inputs in the box that pops
up.

Here is the existing code:

**************************************
Sub InsertROWS()
'
Dim i As Long
Dim j As Long
Dim k As Long

On Error GoTo dontdothat
Do
i = InputBox("How many rows do you want to insert?", "Insert
Rows ", 1)
Loop Until i <> 0
Do
j = InputBox("At what Excel row number do you want to start
the insertion?", "Insert Rows", 10)
Loop Until j <> 0

k = j + i - 1
Range("" & j & ":" & k & "").Insert shift:=xlDown
j = j - 1
Range("A" & j & "").Select
Selection.AutoFill Destination:=Range("A" & j & ":A" & k &
""), Type:=xlFillDefault

Exit Sub

dontdothat:

End Sub
**************************************

Thanks for any help. D

 

[Ken Johnson]

Hi Hypatia,
I was studying your macro when first posted and had a lot of trouble
figuring out what you want changed.
As it stands the User is asked first How many rows to insert (default
1) then where to start the insertion (default row 10). If the defaults
are used the macro then shifts rows 10 and greater down one row and
copies A9 into the new inserted row.
I don't understand what you mean by...

copy from row landed on by macro from A to K of

that empty row to the new location user inputs in the box that pops
up.<

Is "row landed on by macro" row 9 (still referring to the default
inputs)?
Is "the new location user inputs" row 10?
If that is the case then it might only be a matter of changing:

Selection.AutoFill Destination:=Range("A" & j & ":A" & k &...

To:

Selection.AutoFill Destination:=Range("A" & j & ":K" & k &...

BTW, how did my solution for the different fill colors for different
years go?

Ken Johnson

 

[Ken Johnson]

Hi Hypatia,
As well as the last change include changing the previous line from:

Range("A" & j & "").Select

To:

Range("A" & j & ":K" & j).Select

Ken Johnson

 

[StargateFanFromWork]

Hi Hypatia,
As well as the last change include changing the previous line from:

Range("A" & j & "").Select
To:
Range("A" & j & ":K" & j).Select

Brilliant!! It works really great.

I also did what you suggested in the earlier msg re the copy to and from
(sorry about confusion; I muddied the waters, but you understood the
situation exactly despite that <g>) re making the following modification of
the ":A" to ":K". Now that I've seen where the ranges are determined as
there are two examples, I can now re-use much more.:

Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""),
Type:=xlFillDefault
To:
Selection.AutoFill Destination:=Range("A" & j & ":K" & k & ""),
Type:=xlFillDefault

Thanks! D

 

[StargateFanFromWork]

Hi Hypatia,
I was studying your macro when first posted and had a lot of trouble
figuring out what you want changed.
As it stands the User is asked first How many rows to insert (default
1) then where to start the insertion (default row 10). If the defaults
are used the macro then shifts rows 10 and greater down one row and
copies A9 into the new inserted row.
I don't understand what you mean by...
that empty row to the new location user inputs in the box that pops
up.<

Is "row landed on by macro" row 9 (still referring to the default
inputs)?
Is "the new location user inputs" row 10?
If that is the case then it might only be a matter of changing:

Selection.AutoFill Destination:=Range("A" & j & ":A" & k &...

To:

Selection.AutoFill Destination:=Range("A" & j & ":K" & k &...

<lol> Oh, dear. I muddled up my explanation, looks like. You figured it
out despite that, though! (You'll have seen my other post by now perhaps
responding to this post.)

BTW, how did my solution for the different fill colors for different
years go?

It worked great! I've studied it twice but haven't yet quite finished that
sheet. These other XL2K issues and two A2K databases have taken up my
"spare" time <g>. I'm just amazed at how compact the code is, though, and
how it does what it does. Definitely much more advanced than what I know
how to do. I was hoping to get to that sheet this weekend but time didn't
permit. I currently have that sheet as a workout log for myself, hence the
"wkt" found in each header cell. But I will adapt that to what we need here
at the office though only till probably after the election (Jan.23.)! I
have the personal workout version for this, so that got me out of the spot
of having to buy a date book this year for doing the logging into!

Thanks! D