Copy Range of data to the end

[SteynBS]

Hi Guys,

I want to copy data in a range all the way to the last used row. The data will change from time to time. I might have more row or less rows, the columns however will stay the same. VBA codes I have used only copies data to the 1st empty row (over multiple columns) How do I get the VBA code to copy to the last occupied row in the column range.
Column range is from A6:K6 and L6:W6 to the last row

Please assist

 

[Coyotex3]

Are you just trying to copy the range, or are you trying to paste it somewhere else?

 

[SteynBS]

Are you just trying to copy the range, or are you trying to paste it somewhere else?

Yes I will be pasting it in another workbook.

 

[Coyotex3]

Yes I will be pasting it in another workbook.

Thank you. When you say "A6:K6 and L6:W6 to the last row" do you mean A6:W6 and Last row?

 

[SteynBS]

Thank you. When you say "A6:K6 and L6:W6 to the last row" do you mean A6:W6 and Last row?

Not sure, but I need all the data from A6:K6 and L6:W6 up until the last row of occupied data to be copied.

A6:K6 will be pasted in another worksheet (called X) to Column A4:K4. Data from L6:W6 will be pasted in Worksheet (called X) from X4:AI4. Hope it makes sence

 

[Coyotex3]

Not sure, but I need all the data from A6:K6 and L6:W6 up until the last row of occupied data to be copied.

A6:K6 will be pasted in another worksheet (called X) to Column A4:K4. Data from L6:W6 will be pasted in Worksheet (called X) from X4:AI4. Hope it makes sence

This code here should copy your data to the end of your sheet. Unsure if this is exactly what you need.

Sub Test()
Range("A6:W6" & Range("W" & Rows.Count).End(3).Row).Copy
End Sub

 

[Coyotex3]

Don't know if this is exactly what you are looking for but maybe this will get what you want. Make sure to change the sheets name to match what is on your workbook.

Sub Test1()
Dim a, b As Variant
Dim Wb, Wb1 As Worksheet

Set Wb = Sheets("HH")
Set Wb1 = Sheets("XX")

a = Wb.Range("A6:K6").Value
b = Wb.Range("L6:W" & Range("W" & Rows.Count).End(xlUp).Row).Value

Wb1.Range("A4:K4").Value = a
Wb1.Range("X4:AI4").Resize(UBound(b), UBound(b, 2)).Value = b

End Sub

 

[SteynBS]

Don't know if this is exactly what you are looking for but maybe this will get what you want. Make sure to change the sheets name to match what is on your workbook.

Sub Test1()
Dim a, b As Variant
Dim Wb, Wb1 As Worksheet

Set Wb = Sheets("HH")
Set Wb1 = Sheets("XX")

a = Wb.Range("A6:K6").Value
b = Wb.Range("L6:W" & Range("W" & Rows.Count).End(xlUp).Row).Value

Wb1.Range("A4:K4").Value = a
Wb1.Range("X4:AI4").Resize(UBound(b), UBound(b, 2)).Value = b

End Sub

Hi,

Yes this almost works. Range A6:K6 only copies the single row - not the data following below to the next sheet. Range L6:W copies from the 1st row to the 6th row only, not the rest of the data.

This is the source on workbook "Master" Sheet 1 I want to copy Columns A:K from row 6 to the end of the data below. (Changes every time).This will be pasted in another workbook "Sales"Sheet 1 Range A2. Columns L:W from row to the end of the data range in the Master Workbook wil be copied and pasted in Workbook "Sales" Sheet 1 from X2:AI. Hope this makes more sence.

Then as a "would be nice to do" the names in L3:W3 in the "master" Workbook should be added to the names in Workbook "Sales" X1:AI - So basically it would look something like this:
ActiveCell.Value(This is Workbook "Sales" X1) = "Gr Sales" & " " & "MASTER".Sheets(Sheet1).Cells(3,12).Value "Gr Sales being the constant and the Name will change everytime the master data is updated.

 

[Coyotex3]

Hi,

Yes this almost works. Range A6:K6 only copies the single row - not the data following below to the next sheet. Range L6:W copies from the 1st row to the 6th row only, not the rest of the data.

This is the source on workbook "Master" Sheet 1 I want to copy Columns A:K from row 6 to the end of the data below. (Changes every time).This will be pasted in another workbook "Sales"Sheet 1 Range A2. Columns L:W from row to the end of the data range in the Master Workbook wil be copied and pasted in Workbook "Sales" Sheet 1 from X2:AI. Hope this makes more sence.

Then as a "would be nice to do" the names in L3:W3 in the "master" Workbook should be added to the names in Workbook "Sales" X1:AI - So basically it would look something like this:
ActiveCell.Value(This is Workbook "Sales" X1) = "Gr Sales" & " " & "MASTER".Sheets(Sheet1).Cells(3,12).Value "Gr Sales being the constant and the Name will change everytime the master data is updated.
View attachment 73063

Try this new modification in order to try to tackle the first part. Do you have any data at the end of Column("W")?

Sub CopyPasteIt()
Dim a, b As Variant
Dim Wb, Wb1 As Worksheet

Set Wb = Sheets("Master")
Set Wb1 = Sheets("Sales")

a = Wb.Range("A6:K" & Range("K" & Rows.Count).End(xlUp).Row).Value
b = Wb.Range("L6:W" & Range("W" & Rows.Count).End(xlUp).Row).Value

Wb1.Range("A4:K4").Resize(UBound(b), UBound(b, 2)).Value = a
Wb1.Range("X4:AI4").Resize(UBound(b), UBound(b, 2)).Value = b

End Sub

 

[SteynBS]

Try this new modification in order to try to tackle the first part. Do you have any data at the end of Column("W")?

Sub CopyPasteIt()
Dim a, b As Variant
Dim Wb, Wb1 As Worksheet

Set Wb = Sheets("Master")
Set Wb1 = Sheets("Sales")

a = Wb.Range("A6:K" & Range("K" & Rows.Count).End(xlUp).Row).Value
b = Wb.Range("L6:W" & Range("W" & Rows.Count).End(xlUp).Row).Value

Wb1.Range("A4:K4").Resize(UBound(b), UBound(b, 2)).Value = a
Wb1.Range("X4:AI4").Resize(UBound(b), UBound(b, 2)).Value = b

End Sub

All the rows are occupied to the last row. Some does have blanks