Define dynamic ranges with vba

Question

I'm trying to create a macro that copy and paste a dynamic range from a worksheet to another worksheet based on a label at the beginning of each row. I have label on column "O" as 'ItemHeader', 'ItemInfo', 'ItemDesc' & 'ItemURL' and some information on each of these rows ending up on column "AE". SA have a defined width but I need to adjust each range based on its header on each row. I need to find where each group of labels start and end to define my range and them copy and paste to another worksheet. Each group of information such as 'ItemDesc' is together.

Me idea is to use the same macro on different worksheets as this template will be on the same column ("O:AE") but the number of rows will be vary.

Sheets("PBA220").Range("O3:AE3").Copy
Sheets("Carrinho").Visible = True
Sheets("Carrinho").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
    
Sheets("PBA220").Range("O4:AB4").Copy
Sheets("Carrinho").Visible = True
Sheets("Carrinho").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

Sheets("PBA220").Range("AC4:AE4").Copy
Sheets("Carrinho").Select
Range("A" & Rows.Count).End(xlUp).Offset(0, 14).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats

Sheets("PBA220").Range("O5:AE24").Copy
Sheets("Carrinho").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats

Sheets("PBA220").Range("O25:AE26").Copy
Sheets("Carrinho").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll

Thanks

enter image description here

What have you tried? Can you provide your macro? Your question is unclear, you need to provide more information, . Column O is not the beginning of each row. What do you mean by SA have a defined width? Do you need to split each group and save to a different worksheet? Is the data from each worksheet going to be combined into the same worksheet? e.g. rows with "ItemDesc" from each worksheet saved to the same worksheet. The best way would be to filter for the string in Column O and copy the visible data to the new worksheet. — GMalc
@GMalc Here is what i've done so far. My data start at column "O" and end at column "AE", so I need to find some specific labels data I inserted on Column "O" to delimit my range. All data will be copied to a different worksheet called "Carrinho". All data will come from the same worksheet at a time. I'm trying to use different ranges because the paste metrhod will be different for each range. — Felipe Cavalari
Instead of fixed ranges like Sheets("PBA220").Range("O25:AB26").Copy, I want to search where it begin and where it ends automatically, so I can use the same macro for each different worksheet source, I mean, where the copied data will come from and paste to the same "Carrinho" worksheet. — Felipe Cavalari
If you want to select the rows that have ItemDesc in Col O you would use AutoFilter on Col O, then copy the visible data from Col O to Col AE, and paste to the next empty cell in Sheets("Carrinho"). If you rows have different column widths, then you will have to loop through each cell in Col O and select the range to the last column in each row where you find ItemDesc. You need to clarify what you mean by "I need to adjust each range based on its header on each row." — GMalc

Adam Wojciechowski Adam Wojciechowski · Accepted Answer · 2020-08-13T22:35:52

You can use Find method from range. See MS documentation: https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

Use it to find column start and end markers.

Define dynamic ranges with vba

1 Answers