format help

[swqa1234]

Hi All

Please find attached 2 files. 1 is for test data and form, the other is a screen shot of how I would like the form to resemble (formatting etc)

As the data returned into the form is dynamic, I cannot resolve to set the format as per the picture.

If anyone can help, that would be tremendous.

Personal thx to p45cal who helped big time with the original code

cheers....

[jdelano]

You can select the row you want to copy the formatting from using Range(*row number*).Rows.EntireRow.Select and then Selection.Insert xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow and then write the data to the newly created row.

Here is an example from code I created to help someone do something similar


For rw = 1 To countPackingSlips - 1
BlankTemplateSheet.Range("3:3").Rows.EntireRow.Select
Selection.Insert xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
BlankTemplateSheet.Cells(rw + 2, 1).Value = uniquePS(rw - 1) ' write the packing slip to the cell
Next rw



It creates a new row in the template by first copying the last row in the template and writes some data to the new row.

[swqa1234]

hi and thx for the response. I did try it however not sure I understood it exactly and when run, it failed at point uniquePS

In case I was not clear in my query, I'm looking for code to wrap the dynamic and undermined data returned in a frame similar to the one in the picture

cheers

[jdelano]

That was a n example of Range.EntireRow property (Excel) | Microsoft Learn (https://learn.microsoft.com/en-us/office/vba/api/excel.range.entirerow) and Range.Insert method (Excel) | Microsoft Learn (https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Insert) you need to apply them to your workbook separately.