Change a range dynamically - VBA script. [Solved/Closed]

Report
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
-
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
-
Hi everyone.

I am stuck on a small problem with writing a Macro in Excel and would appreciate some help.

I have the following script:

For Each c In ActiveSheet.Range("A4:AL54,AM4:AN34").SpecialCells(xlCellTypeBlanks)
c.Select
ActiveSheet.Paste
pastes_left = pastes_left - 1
Exit For
Next c

This pastes a value from one sheet of my spreadsheet into vacant spaces in the specified range on a different sheet (the now active sheet). However, when the spaces in this range are full (so there is nowhere to paste the data), I need to select the same range, but on a different sheet and paste the data into vacant cells on that (different) sheet. The "overflow" sheet already exists with random spaces in the range required. The pastes_left variable counts down every paste so the script knows when to change pages (ie when pastes_left=0).

My question is ... how do I specify the new range(sheet) so that it fills all the spaces in sheet one and then continues to paste the remainder into sheet 2? I think Iwant the range in my script to be a variable so when pastes_left=0 the script says "Ok, now that range is full I want to go onto the next range."


I hope I have explained in a clear enough fasion and look forward to your comments.

2 replies

Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
I think this is what you wanted. Not positive though.
It all seems a bit clunky to me. There must be a better way to do this.

Hopefully I have interpreted your spec correctly.


For testing I assigned a value of 20 for the number of Pastes to be done.
It will loop through all the blank cells in a sheet and paste the value into each of them until the number reaches zero when it will then reset the number of pastes and change to the next sheet.

What is supposed to happen if the number of pastes is greater then the number of blank cells in the sheet? Is that likely to ever happen?

If there are no cells in the range an error occurs and so it will exit the sub.


Sub pasterange()
Dim xWs As Worksheet
Dim c As Range
Dim I as Integer

'*******************************
' Copy Value for Testing Only
Sheet3.Range("E1").Copy ' Pre-commit sheet
'*******************************

' This is required as it will cause an error if no empty cells are found
On Error GoTo Exit_Loop

' Start at the 2nd Sheet, assume 1st sheet is the Pre-Commit sheet
For i = 2 To Application.Sheets.Count

pastes_left = 20 ' Set the number of pastes for the sheet

Set xWs = Sheets(i)
xWs.Activate

For Each c In xWs.Range("A4:AL54,AM4:AN34").SpecialCells(xlCellTypeBlanks)
xWs.Range(c.Address).PasteSpecial Paste:=xlPasteValues
pastes_left = pastes_left - 1
If pastes_left = 0 Then Exit For
Next c
Next i

Exit_Loop:

'Clear the clipboard
Application.CutCopyMode = False

End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3990 users have said thank you to us this month

BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
213
Thanks Ray,

This is a mess - my description is awful!

I will look at your code tomorrow - its 12:30AM here - I put my bad description down to tiredness.

Your code looks good at first sight - but if I dont get on with it I will add another description tomorrow. (or soon after)

Thanks again
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
213
Thanks Ray,

This works (with a very slight modification to take the pastes_left from the existing script).

Now I have a new problem - The peramiters of the project have changed and I think I need to make this more modular in its approach bringing in sub routines as and when needed (a bit like "includes" on a php web site). I was hoping to do this as a linear script, but it seems other departments have heard of what Im trying to do and want to bolt more thins onto it. Great for learning, but I just want to get this out of the way!

I hate it when that happens!

At least you script will be part of the final thing :o)

Thanks again ... and please be prepared for more questions later.
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
As you probably know, as it stands the code you provided does not work.

Is this all the code there is?

Before I attempt to supply a solution I have a few of questions for you:

1. Where is the pastes_left value coming from?
What is the starting value?
Is this starting value different per sheet?
To clarify this value is reset to the starting value at the start of each
sheet. Correct?

2. What are you copying that you want to paste?
Is it a Cell value?
Is it a value that you have added to the clipboard via Ctrl-C?

3. Does this affect ALL sheets in the workbook?

Ray
BrianGreen
Posts
1005
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
October 2, 2019
213
Thanks Ray,

Yes - the code supplied is a nippet of the whole code, but I will try to answer your questions. A story fasion is perhaps the best way so I can explain what happens and where I am up to now.

Ok - a store of product types (stored as strings) is to be kept and depending on what they are they are stored on different sheets of an excel workbook. The user puts the products into sheet 1 and they get transfered to the correct sheets after a button is pressed. The reason for this is that in the past people have put the data into the wrong sheets. The first page gives a chance to check the numbers are correct before being committed.

The pastes_left is taken as a count of all the products that the user puts in.

When the sheet is full a second sheet is to be invoked so other users can do bits they need to - I dont know why its not all on one sheet, but thats the way it has to be ...

so the pastes_left needs to be the same as it was on the previous sheet (soe every paste is counted for and when the count gets to zero then the macro ends.

The value is a string that is copied to the clipboard.

No - the whole workbook is not affected - only the sheets that need to be updated.

Thanks in advance for any help you can give.