CODEWITHSUNDEEP
vbaexcel
Abdul Marafi
Abdul Marafi

Reputation: 15

Macro : Copy Range to Next Consecutive Blank Column(s)

everyone. Newbie here.

I have tried to the best of my knowledge and ability to go through the hundreds of macro codes that allows the user to specifically click a button to copy and paste a range into the next blank column, and keep pasting new columns as long as the user clicks the button. Unfortunately, the small variations in the purpose of the codes created will differ. I am unable to understand why.

I have managed last night to hit the jackpot and make it work flawlessly, until it crashed for some reason just as I saved the file. It didn't work after that.

The objective: for analysis purposes, the range in C9:C43 will always be there as a base year data. If the user has one year worth of data, then no need to copy. If the user has 5 years worth of data, the user will click the button 5 times. The copy will include formats, formulas, and column widths.

Furthermore, considering that the range in column C is the base, cell C9 will resemble the Year i.e. 2015. If the user changes C9 to, say, 2010, the following column will have D9 = 2011, and so on.

I thought a dialogue box will be a better representation of the objective I'm working on, but seems to be far-fetched with my current understanding of Macros and VBA.

Here is the code I used last night that got it working before it crashed mysteriously after saving the file:

Dim rngSource As Range
Dim rngDestination As Range

Set rngSource = Range("C9:C43")
Set rngDestination = Cells(9, Columns.Count).End(xlToLeft).Offset(0, 1)

Range("C9:C43").Copy
Range("D9").Select

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Range("D9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]+1"

rngSource.Copy destination:=rngDestination

You guys are amazing and your work and cooperation is deeply appreciated.

Warm regards,

Upvotes: 0

Views: 53

Answers (1)

jamheadart
jamheadart

Reputation: 5343

While this doesn't answer your question about freezing code, I believe it may help you with your fundamental problem of copying columns in a series

If you use cell A1 for a user to specify how many columns they would like to add, then a button press could call range.autofill with a series fill (this works for the year but I'm not sure what you have underneath that top row)

Sub Button_Click()

Dim x As Integer    
x = Range("A1").Value
Range("C9:C43").AutoFill Destination:=Range("C9:" & Cells(43, x + 3).Address), Type:=xlFillSeries

End Sub

Upvotes: 1

Related Questions