VBA Attempting to Insert New Rows but Only needing Column A copied

[horizonblue]

Hi,

Im using this VBA code at the moment.

Sub Macro1()
Dim i As Long, n As Variant
n = InputBox("How many rows:", "INSERT ROWS")
If n = "" Or Not IsNumeric Or n < 1 Then Exit Sub
If Int < Val Then Exit Sub
i = 10
Do While Cells(i, "B") <> ""
i = i + 1
Loop
Rows(i & ":" & i + n - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows(i - 1 & ":" & i - 1).Copy
Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormulas
Rows(i & ":" & i + n - 1).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub


The problem is, that it is copying everything in the previous rows. I am only wanting it to copy column A. Does anyone know how to fix this?

I'm also needing help to ensure that every time a new row is added that the reference number ticks over to the next numerical value.

 

[jolivanes]

Did you get it to work?

 

[horizonblue]

Hi,

Unfortunately it is still adding the ref number into the wrong format. I think the picture I attached wasn't an accurate depiction of what I wanted, apologies. I am needing it to read VBA-027/24-25, and then when a new row is added for it to automatically change to VBA-028/24-25, VBA-029/24-25 ect. So, each time a new row is added the number is updated automatically.

Thank you

 

[jolivanes]

We all know that you can't help people who don't want to help themselves.
If you don't answer the questions from people that are willing to, and spending their free time to help you, not much can be achieved.
This is in regards to Post #20

In regards to your Post #22.
What happens when you run the code we gave you somewhere around 2 weeks ago in Post # 17.

 

[horizonblue]

You forgot to mention which line caused the error.

This line gives me the error - Cells(i, 1).Value = Left(Cells(i - 1, 1), 4) & Format(Mid(Cells(i - 1, 1), 5, 3) * 1 + 1, "000") & Mid(Cells(i - 1, 1), 8)

 

[jolivanes]

What is the formula in the last cell with a value (VBA-***/**-**) in it? In the attached picture in your first post, it shows rows 6 as the last row with data and the row below it empty.
Are there formulas below this cell?

 

[horizonblue]

What is the formula in the last cell with a value (VBA-***/**-**) in it? In the attached picture in your first post, it shows rows 6 as the last row with data and the row below it empty.
Are there formulas below this cell?

Hi, there is no formula in the last cell with the value, it's just flash formatting. There are no formula's below that cell.

 

[jolivanes]

I don't know what flash formatting is but see if this helps.
In the macro change this line

lr = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

to this

lr = Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1).Row

Also, have you checked if the values have any leading or trailing spaces?
There is something that we can't see because a test workbook I made works like a charm.

 

[horizonblue]

I don't know what flash formatting is but see if this helps.
In the macro change this line

lr = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

to this

lr = Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1).Row

Also, have you checked if the values have any leading or trailing spaces?
There is something that we can't see because a test workbook I made works like a charm.

Thank you, appreciate your time on this. Flash formatting is where excel automatically numbers each row when you drag down the cells.

Due to it being a work file, I can't show the actual workbook, however the replication i have made and attached a picture of is the same. However it's still giving me an error in this line of code Cells(i, 1).Value = Left(Cells(i - 1, 1), 4) & Format(Mid(Cells(i - 1, 1), 5, 3) * 1 + 1, "000") & Mid(Cells(i - 1, 1), 8). The error is Run-Time Error '13' type mismatch.

 

[jolivanes]

With the change suggested in Post #27 and the following
Change this line

Cells(i, 1).Value = Left(Cells(i - 1, 1), 4) & Format(Mid(Cells(i - 1, 1), 5, 3) * 1 + 1, "000") & Mid(Cells(i - 1, 1), 8)

to this

Cells(i, 1).Value = Split(Cells(i - 1, 1), "-")(0) & "-" & Format(Split(Split(Cells(i - 1, 1), "/")(0), "-")(1) * 1 + 1, "000") & "/" & Split(Cells(i - 1, 1), "/")(1)

does that make a difference?

BTW, if you run this, do you get the row number of the one below the last cell with data?

Sub Get_First_Empty()
Dim lr As Long
lr = Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1).Row
MsgBox lr
End Sub

 

[horizonblue]

With the change suggested in Post #27 and the following
Change this line

Cells(i, 1).Value = Left(Cells(i - 1, 1), 4) & Format(Mid(Cells(i - 1, 1), 5, 3) * 1 + 1, "000") & Mid(Cells(i - 1, 1), 8)

to this

Cells(i, 1).Value = Split(Cells(i - 1, 1), "-")(0) & "-" & Format(Split(Split(Cells(i - 1, 1), "/")(0), "-")(1) * 1 + 1, "000") & "/" & Split(Cells(i - 1, 1), "/")(1)

does that make a difference?

BTW, if you run this, do you get the row number of the one below the last cell with data?

Sub Get_First_Empty()
Dim lr As Long
lr = Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Offset(1).Row
MsgBox lr
End Sub

Hi! You're amazing, that last code update is now working perfectly. Thank you so much!

Yes the other code does show the row number of the one below the last cell with data.

Thank you again, and appreciate your patience