PDA

View Full Version : Adjust placement of copy paste to suit last row of previous paste



RINCONPAUL
06-02-2016, 03:36 AM
I have a macro that copies a set number of rows containing text, on one sheet and pastes in another sheet. The capture height of rows specified in the macro, suits the max number of possible rows that can be filled with text each capture. Most of the time, the number of rows in the capture is far less than max. With this code the next paste starts the row after the max preset number of rows, leaving a varying gap of blank rows between text. I then have to manually delete all but one blank row manually each day (200+). I require one blank row between subsets of text that's been pasted.

Can this code be modified to always leave one blank row between pastes of varying row height? Cheers.
The code I use:

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


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


copySheet.Range("CN2:DH24").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

mancubus
06-02-2016, 04:06 AM
pls use code tags.



Sub Copy_Race()
Worksheets("BOTT").Range("CN2:DH24").Copy
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
End Sub

SamT
06-02-2016, 07:17 AM
Worksheets("BOTT").Intersect(UsedRange, Range("CN:DH")).Copy

RINCONPAUL
06-02-2016, 11:49 AM
No good boys! Runtime error '424' with mancubus and Runtime error '438' with SamT.

With SamT's post, I inserted your line in lieu of "copysheet...." in my code. By the way there is other text under the table (below Row 24) that is static and not being renewed, so don't want to copy that! I trialled both code offerings on a blank sheet with just one subset of text to copy to simplify things. I think my original post might have been misread somewhat, so I'll try again:

1st Copysheet:
Text, Text, Text
Text, Text, Text
Text, Text, Text
blank
blank
blank

2nd Copy:
Text, Text, Text
Text, Text, Text
blank
blank
blank
blank

3rd Copy:
Text, Text, Text
Text, Text, Text
Text, Text, Text
Text, Text, Text
Text, Text, Text
blank

Pastesheet:

Text, Text, Text
Text, Text, Text
Text, Text, Text
blank
Text, Text, Text
Text, Text, Text
blank
Text, Text, Text
Text, Text, Text
Text, Text, Text
Text, Text, Text
Text, Text, Text
blank

mdmackillop
06-02-2016, 12:02 PM
Sub Copy_Race2()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim Target As Range

Set Target = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2)
Set copySheet = Worksheets("BOTT")

copySheet.Range("CN2:DH24").Copy
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

RINCONPAUL
06-02-2016, 12:22 PM
Like Matt Damon in 'Good Will Hunting', you've just walked past a blackboard and solved the unsolvable....LOL Thanks so much.:beerchug:

mancubus
06-02-2016, 11:03 PM
Sub Copy_Race1_v2()
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(23, 21).Value = Worksheets("BOTT").Range("CN2:DH24").Value
End Sub




Sub Copy_Race1_v1()
Worksheets("BOTT").Range("CN2:DH24").Copy
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2).PasteSpecial xlPasteValues
End Sub


both worked for me.

RINCONPAUL
06-02-2016, 11:35 PM
Yes, I see what you mean? Also, mdmackillop's worked on fresh sheets too, but for some reason, they won't work on my real-time sheet? The copy still pastes after the 24th row range as set by the copy. Would it be some sort of format (unseen) that's carried over from the "BOTT" sheet occupying those blank cells? I know the cells in the range are formatted as percentages and currency. It's as if the macro recognises something occupying those cells, even though blank to the naked eye?

RINCONPAUL
06-02-2016, 11:39 PM
Also the copy range contains conditional colour formatting.

RINCONPAUL
06-03-2016, 12:01 AM
Further to the last two, those cells that are copied and appear as blanks contain formulas, that only populate text when other conditions are met. Does copying and pasting a blank cell, with various formats and formulas, read as an occupied cell by the macro script?

mancubus
06-03-2016, 12:22 AM
upload your workbook. (see my signature.)

if it is an imported data from a 3rd party application, it's quite possible that it contains non printinng characters, etc.

RINCONPAUL
06-03-2016, 01:33 AM
Well that probably explains it then, as BOTT sheet is connected to a 3rd party program that updates live prices. Hence why I can't upload it to you as it wouldn't work and replicate live conditions. Ahh well, just have to persevere. Thanks for your interest and help.

mancubus
06-03-2016, 01:43 AM
you can trim/clean data after importing in to excel.

GGIYBF here. search for: "excel vba remove non printable characters"

RINCONPAUL
06-03-2016, 02:10 AM
you can trim/clean data after importing in to excel.

GGIYBF here. search for: "excel vba remove non printable characters"

after looking for answers on other forums, it seems that because the cells have an IF formula that returns a "" if no input, that is deemed a null character! The macro would need to be more dynamic and either not select those cells at the copy sheet stage, or clear the copied cells after the paste, and before the next paste.

mdmackillop
06-03-2016, 05:10 AM
You could also try

'Pseudo code
Range1.Value = Range2.Value
'and if required
Range2.copy
Range1.Paste PasteSpecial:= XLFormat

SamT
06-03-2016, 06:57 AM
?
Worksheets("BOTT").Range(Intersect(Range("CN2").CurrentRegion, Range("CN:DH"))).Copy

RINCONPAUL
06-03-2016, 05:29 PM
With a new dawn, comes a fresh try, and this code is working beautifully today:


Sub Copy_Race1_v2()
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(23, 21).Value = Worksheets("BOTT").Range("CN2:DH24").Value
End Sub

Sam, your code still throws an error. Thanks to all for your contributions.