Column Combine VBA

[masonbp2]

Hello,

I am trying to combine several columns into one column, pasted on another sheet. I am getting an error of "Error 1004 Method 'Range' of object '_Worksheet' failed" when running my code. I am trying to use range.cells() so that I can reference columns in a for loop. At the top of each column from the copy sheet, I have a cell that has the numerical value for the last non blank cell in the column.

Sub Second_Round()


Dim targetworkbook As Workbook
Set targetworkbook = Application.ActiveWorkbook
Dim targetSheet As Worksheet
Set targetSheet = targetworkbook.Worksheets("Second Round Sample")
Dim copysheet As Worksheet
Set copysheet = targetworkbook.Worksheets("Second Round")
Dim pasterange As Range
Dim lastrowa As Integer




Application.Calculation = xlCalculationManual


Set pasterange = targetSheet.Range("A2")


For c = 2 To 50




lastrowa = copysheet.Cells(1, c).Value
copysheet.Range(Cells(3, c), Cells(lastrowa, c)).Copy
pasterange.PasteSpecial xlPasteValuesAndNumberFormats

pasterange = targetSheet.Range("A" & pasterange + lastrowa)


Next c

Application.Calculation = xlCalculationAutomatic


End Sub


I would appreciate any help.

 

[masonbp2]

The error is occurring on copysheet.Range(Cells(3, c), Cells(lastrowa, c)).Copy

 

[Fluff]

You need to qualify the Cells, as well as the range

copysheet.Range(copysheet.Cells(3, c), copysheet.Cells(lastrowa, c)).Copy

 

[DanteAmor]

If the active sheet is copysheet ("Second Round"), you should not have problems. But if you are on another sheet, then you must put the reference, as Fluff says.


This line also changes:

pasterange = targetSheet.Range("A" & pasterange + lastrowa)

For this:

Set pasterange = targetSheet.Range("A" & pasterange.Rows.Count + lastrowa)

 

[masonbp2]

Thank you!! I have slightly changed the code now, as I was getting a mismatch error on pasterange = targetSheet.Range("A" & pasterange + lastrowa) as one was a integer and one was a range. This is my new code. I am getting an error of object variable or with block variable not set:

Sub Second_Round()


Dim targetworkbook As Workbook
Set targetworkbook = Application.ActiveWorkbook
Dim targetSheet As Worksheet
Set targetSheet = targetworkbook.Worksheets("Second Round Sample")
Dim copysheet As Worksheet
Set copysheet = targetworkbook.Worksheets("Second Round")
Dim pasterange As Range
Dim lastrowa As Integer
Dim pastenumber As Integer




Application.Calculation = xlCalculationManual


pastenumber = 2


For c = 2 To 50




lastrowa = copysheet.Cells(1, c).Value
copysheet.Range(copysheet.Cells(3, c), copysheet.Cells(lastrowa, c)).Copy
pasterange = targetSheet.Range("A" & pastenumber)
pasterange.PasteSpecial xlPasteValuesAndNumberFormats

pasterange = targetSheet.Range("A" & pastenumber + lastrowa)


Next c
Application.Calculation = xlCalculationAutomatic


End Sub

 

[DanteAmor]

You can read my answer in post #4 about the pasterange object

 

[Fluff]

Try

For c = 2 To 50
   lastrowa = copysheet.Cells(1, c).Value
   copysheet.Range(copysheet.Cells(3, c), copysheet.Cells(lastrowa, c)).Copy
   Set pasterange = targetSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
   pasterange.PasteSpecial xlPasteValuesAndNumberFormats
Next c

 

[masonbp2]

Thank you guys so much!! I was able to complete my file after a very stressful day because of you.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DanteAmor]

You're welcome