Insert Copied Rows every 40 lines

[adwatson]

Hi All,
I have searched but was unable to find what I was looking for. I have a set of rows that I have copied in an Excel table. I then want to insert these copied rows every 40 lines but with a user input box that lets you choose which row to start at. For instance user can input row 45 and copied rows will be inserted there, then move down 40 rows and insert again and repeat until Col B is blank. Any help would be greatly appreciated, I am still learning to use loops.

Thanks!

 

[adwatson]

Thanks again for your help. I agree that you must keep a copy of the original data, but the sheet I am working from is several copies away from the original so I feel very comfortable inserting the headers at this point.

thanks!

 

[adwatson]

Ok so I am very close to being complete with this and although I would prefer to just insert the header every 40 lines in the sheet I already have I can make this work but the problem is that even after removing the final line of code the columns do not stay the same size as the original columns. I have not been able to get any of my changes to keep the original column sizes. I have modified my code to make it work for a dynamic number of columns (it will vary and not always be B:D) and that is working just fine, but that is why I need it to basically paste special:column widths because I will not be able to adjust the columns widths individually as each column will be a different width and will vary every time.

Any suggestions?

thanks!

 

[Marcelo Branco]

Hi,

Try something like this

destinationWk.Range("A:A").ColumnWidth = originWk.Range("A:A").ColumnWidth

Adjust the columns and worksheets objects to your real case.

HTH

M.

 

[adwatson]

Hey thanks for your reply. I worked something like that out for both the column widths and row heights, and that seems to be working ok, except for the headers are a different row height than the other cells so since the headers are not in the original sheet(except for at the top of my table) they get resized to the original row height of the data. I haven't figured out a work around for this yet.

 

[Marcelo Branco]

Hi,

I've not tested but maybe (not sure) you can, during the copy process inside the macro, adjust the height of each row.

Something like
After
hdrRange.Copy Destination:=.Range("B" & lin)

insert
.Range("B" & lin & ":B" & lin + 2).RowHeight = hdrRange.RowHeight

M.

 

[adwatson]

Hey. I was very hopeful that would work but when I tried it on my test book it just skipped copying in the line so it seems to interfere somehow.

 

[Marcelo Branco]

This worked for me

With destinationWk
        For i = 4 To lastRow Step 40
            hdrRange.Copy Destination:=.Range("B" & lin)
            originWk.Range("B" & i & ":D" & i + 39).Copy _
                Destination:=.Range("B" & lin + 3)
          [COLOR=royalblue] [B].Rows(lin & ":" & lin + 2).RowHeight = hdrRange.RowHeight[/B][/COLOR]
            lin = lin + 43
        Next i
End With

HTH

M.

 

[adwatson]

I must be doing something wrong. I copied your code exactly and tried it on my test book and the row heights did not change at all.

 

[adwatson]

Ok so I figured out that the reason this is not working is because the 3 header rows have different row heights. This first header is larger than the other 2. If they are all the same size then you are right this works perfectly. So now I will see if there is a work around for this.

 

[adwatson]

Ok you have been a ton of help. I actually modified your original code and got it to insert the headers every 40 lines and this is working perfectly! Thanks so much!