Copy and Paste into multiple columns and different rows

[TomNR]

Hi all,

I am trying to get two pieces of data from one Workbook and paste it into another Workbook but in a specific cell.

I am pretty new to VBA so am using ActiveCell currently. However, the problem is I am copying over two values and so it just over writes one another.

So my question is, how can I get the data to be pasted into Row 2 but it columns B & C and then next time I hit the macro it goes to Row 3 and column B & C.

The code I have so far is:

Sub kcl3()
Const book2Name = "paper.xls"
    ' declarations
Dim book1 As Workbook
Dim book2 As Workbook
   
    ' start
Set book1 = ThisWorkbook
Set book2 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\paper.xls")
 
book2.Sheets("1 - Project on a page").Range("N22:N27").Copy
book1.Sheets("October 15").Activate
ActiveCell.PasteSpecial xlPasteValues
 
book2.Sheets("1 - Project on a page").Range("N34:N39").Copy
book1.Sheets("October 15").Activate
ActiveCell.PasteSpecial xlPasteValues
 
End Sub

 

[Deepak]

Hi,

If the cells have a specific data like as blank,"abc" then you can fire it from macro else you need to store it locally in sheet that where you paste last time.

 

[TomNR]

Hi,

If the cells have a specific data like as blank,"abc" then you can fire it from macro else you need to store it locally in sheet that where you paste last time.

The cells don't have specifc data so it will have to be copied over from the other Workbook.

Could you explain wht you mean by 'store it locally in sheet that where you paste last time.'

 

[Deepak]

Would be pls upload the sample data.

Could you explain wht you mean by 'store it locally in sheet that where you paste last time.'

like put something in code

x=[AAZ1]
copy to row x

[AAZ1]=[AAZ1]+1

 

[Luke M]

You're copying a vertical range of 5 cells, so I'm not sure completely how you want it to end up in columns B&C (only 2 cells). But taking a long shot, perhaps something like this:

Sub kcl3()
Const book2Name = "paper.xls"
    ' declarations
Dim book1 As Workbook
Dim book2 As Workbook
   
    ' start
Set book1 = ThisWorkbook
Set book2 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\paper.xls")

book2.Sheets("1 - Project on a page").Range("N22:N27").Copy
'Paste into B2, transpose cells so they go horizontal rather than vertical
book1.Sheets("October 15").Range("B2").PasteSpecial Paste:=xlPasteValues, Transpose:=True

'Repeat for the 2nd range
book2.Sheets("1 - Project on a page").Range("N34:N39").Copy
book1.Sheets("October 15").Range("B3").PasteSpecial Paste:=xlPasteValues, Transpose:=True

End Sub

 

[TomNR]

book1.Sheets("October 15").Range("B3").PasteSpecial
End Sub

Just looking at the above, is there anyway that you don't have to specify a cell.

So basically I want to be able to run the macro once and the data goes into B3.

Run it again and it goes to B4.

And again B5 etc?

Thanks for your help so far!

 

[Luke M]

Is there anything below those cells? Or a better question, what is special about those cells? Is it just the next blank cell in the column (with nothing else below it)?

If yes, you could do something like

With Worksheets("Sheet1")
.Cells(.Rows.Count,"B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With

By using the End method, you keep finding the last cell in the column, and then paste one below that. Is that what you're looking for?

 

[TomNR]

Is there anything below those cells? Or a better question, what is special about those cells? Is it just the next blank cell in the column (with nothing else below it)?

If yes, you could do something like

With Worksheets("Sheet1")
.Cells(.Rows.Count,"B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With

By using the End method, you keep finding the last cell in the column, and then paste one below that. Is that what you're looking for?

Sorry for the late reply, I was away on long weekend holiday

Thanks for the help again Luke! The End method was exactly what I was looking for!

Sorry for the basic questions (at least I think it is basic) but it is really helping me learn more about VBA. I did try Googling it before asking on here but I couldn't find the answer and you hit it right on the head!

It is why this forum is so awesome!!!

Thanks again Luke!