VBA Code - Copy set range and paste in the next blank row (skipping 1 row)

[Franchise1979]

Hi All, I have a very small and easy spreadsheet in which I am trying to do a macro for the following:

1) Copy cells B5:J19 (including formats)
2) find the next row with no data
3) paste in the next row with no data (but skipping 1 more row so there is a space)

Here is the code I am using:

Sub PasteSource()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet

  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("Sheet1")

  copySheet.Range("B5:J18").Copy
  pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

The offset 2 I know is adding the extra row I need but for some reason, it is pasting the data (values only, which I know I have PasteValues in the code) 4 columns over to the right.

Notes:
1) I am perfectly fine copying the entire rows (5-19) vs a range
2) The range will always be pasted in the same worksheet
3) Worksheet name could change (so thinking "sheet1" needs to be simply active sheet so I remove sheet naems out of the equation?

Any and all help is appreciated.

Thank you!!!!!

 

[rollis13]

Change your line to this:

pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(2, -4).PasteSpecial xlPasteValues

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

1) Copy cells B5:J19 (including formats)

I'm confused. You specify that formats are to be copied, but your code pastes values only and not formats. Can you clarify exactly what should be happening?

2) find the next row with no data

Your code is using column F (6) to determine that. Would column B also work to determine the last row used?

3) Worksheet name could change (so thinking "sheet1" needs to be simply active sheet so I remove sheet naems out of the equation?

If everything is happening on the active sheet then you can remove all references to worksheets and simply refer to the ranges.

 

[bebo021999]

Try this to paste value AND format

Option Explicit
Sub copypaste()
Dim lr&

'define last used row of range(B5:J19), then add 2
lr = Range("B5:J19").Find(what:="*", searchDirection:=xlPrevious).Row + 2

'---------
'In case of multiple paste, with destination one after one downwards:
'lr = Range("B5:J100000").Find(what:="*", searchDirection:=xlPrevious).Row + 2
'--------

Range("B5:J19").Copy
With Range("B" & lr) ' destination cell
    .PasteSpecial Paste:=xlPasteValues ' paste value
    .PasteSpecial Paste:=xlPasteFormats ' paste format
    .Select
End With
Application.CutCopyMode = False
End Sub

 

[Franchise1979]

Try this to paste value AND format

Option Explicit
Sub copypaste()
Dim lr&

'define last used row of range(B5:J19), then add 2
lr = Range("B5:J19").Find(what:="*", searchDirection:=xlPrevious).Row + 2

'---------
'In case of multiple paste, with destination one after one downwards:
'lr = Range("B5:J100000").Find(what:="*", searchDirection:=xlPrevious).Row + 2
'--------

Range("B5:J19").Copy
With Range("B" & lr) ' destination cell
    .PasteSpecial Paste:=xlPasteValues ' paste value
    .PasteSpecial Paste:=xlPasteFormats ' paste format
    .Select
End With
Application.CutCopyMode = False
End Sub

This works perfectly! Thank you!
The only challenge now....When I run the macro, it will copy and paste b5:J19 in B20:J34, which is exactly what I want it to do. However, if I run it again, it paste in the same 20-34 rows...I would want it to go to 35-49 etc...
Does that make sense?

 

[Peter_SSs]

Do you really need PasteSpecial?

If "No"

Sub CopyBlock_v1()
  Range("B5:J18").Copy Destination:=Range("B" & Columns("B:J").Find(What:="*", SearchDirection:=xlPrevious).Row + 2)
End Sub

If "Yes"

Sub CopyBlock_v2()
  Range("B5:J18").Copy
  With Range("B" & Columns("B:J").Find(What:="*", SearchDirection:=xlPrevious).Row + 2)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
  End With
  Application.CutCopyMode = False
End Sub

 

[bebo021999]

This works perfectly! Thank you!
The only challenge now....When I run the macro, it will copy and paste b5:J19 in B20:J34, which is exactly what I want it to do. However, if I run it again, it paste in the same 20-34 rows...I would want it to go to 35-49 etc...
Does that make sense?

That what I left a second choice for you

'define last used row of range(B5:J19), then add 2
lr = Range("B5:J19").Find(what:="*", searchDirection:=xlPrevious).Row + 2

'---------
'In case of multiple paste, with destination one after one downwards:
'lr = Range("B5:J100000").Find(what:="*", searchDirection:=xlPrevious).Row + 2
'--------

Replace 1st lr (current using) with 2nd lr

lr = Range("B5:J100000").Find(what:="*", searchDirection:=xlPrevious).Row + 2

 

[Franchise1979]

This all worked perfectly!!! Thank you to all who have helped.

Do you know if there is a way when I copy the range B5:J19 and paste in the next set of empty row (which works so perfectly btw if I have not mentioned), but I would like to delete any data in columns B:E and G:I (in the newly pasted section, not in the original rows 5-19)

So if you can imagine, it is a template and text in column F (F5:F19) is always static (never changes) and column J (J5:J19) is a formula, so I do not want to step on the formula

If not no biggie, I will just push my button and than delete the data when it is pasted.

Thank you all so much again, I truly mean it.

 

[Peter_SSs]

but I would like to delete any data in columns B:E and G:I (in the newly pasted section, not in the original rows 5-19)

So you didn't really want columns B:E (or G:I) copied in the first place?

See if this is what you want then.

Sub CopyBlock_v3()
  Range("F5:J18").Copy
  With Range("F" & Rows.Count).End(xlUp).Offset(2)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
    .Offset(, 1).Resize(14, 3).Clear
  End With
  Application.CutCopyMode = False
End Sub

 

[Franchise1979]

So you didn't really want columns B:E (or G:I) copied in the first place?

See if this is what you want then.

Sub CopyBlock_v3()
  Range("F5:J18").Copy
  With Range("F" & Rows.Count).End(xlUp).Offset(2)
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
    .Offset(, 1).Resize(14, 3).Clear
  End With
  Application.CutCopyMode = False
End Sub

Hi Peter, thanks for the reply! I did want to copy and paste, my apologies if I am not explaining correctly.

I have a template sheet and the first step is to make a copy of the sheet and rename it to whatever Firm we are documenting projects with. If you could imagine the following:
Col A: Blank, used for format spacing etc (open field)
Col B: Has a validation list and is conditionally formatted
Col C: project code (open field)
Col D: project description (open field)
Col E: Has a validation list and is conditionally formatted
Col F: List of sub items in every project (these are static, hence why once I copy and paste I do not want to delete these cells)
Col G: Has a validation list and is conditionally formatted
Col H: Date (open field)
Col I: Timing (open field)
Col J: Formula driven cell based off data in Col H & I

Each Firm could have multiple projects, hence what the code is doing. It copies the range B5:J19 and pastes below the initial subset of items.

I am thinking for simplicity purposes doing the following:
1) Add a macro + button to create a copy of the template sheet
2) Adjust the code to copy + paste range B5:J19 (from the "Template" sheet, not the active sheet) >>.this way I do not need any coding around "delete data in B:E, G:I" in the newly pasted section.

Does that make more sense explaining it that way?

Thanks again!