Pasting into a specified range of cells in the next blank row

[mark111]

Hello Guys,

I need modify this vba code to be able to past into the next specified range in the next blank row, instead of having to select the row to be pasted into every time

here is the code

Windows("mark.xlsx").Activate
Range("C5:G5").Select
Selection.COPY
Windows(sam.xlsb").Activate
sheets("2").select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End sub

many thanks inn advance

 

[Chihiro]

No need to activate window. Nor is there need to select sheet/range.

Can you upload sample workbooks with data?

Also, I've got few questions.

1. I'm assuming sam.xlsb is the destination and contains the code?
2. What condition triggers this code?
3. Is C5:G5 range always fixed?

 

[mark111]

hello, yes sir, sam.xlsb is the destiniation, and contains the code, i trigger this code with macro button manually so no condition, c5:g5 is fixed always.

the workbooks that contains the iinfo are relatively large and protected, if necessary I can modify and send.
Many thanks

Mark swan

 

[Chihiro]

Something like this. But without knowing your exact set up. You may need to adjust few settings/lines. If you have mark.xlsx open when you run the code, remove first block and replace with "Set markWb = Workbooks("mark.xlsx")".

Sub Test()
Dim markWb As Workbook
Dim path As String
Dim lRow As Long

'Change path to your workbook path
path = "C:\Test\mark.xlsx"
Set markWb = Workbooks.Open(path)

'If not the first sheet, change as needed
markWb.Sheets(1).Range("C5:G5").Copy

lRow = ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1

'If data should not be in column A:E, then change column # from 1 to something else
ThisWorkbook.Sheets(2).Cells(lRow, 1).PasteSpecial xlPasteValues

'Save and close source workbook
markWb.Close True

Set markWb = Nothing

End Sub

 

[mark111]

many thanks, regarding this part,
markWb.Sheets(1).Range("C5:G5").Copy

can I replace sheets(1) with activate , because the source workbook each day has a new sheet, and I copy the info from the current open sheet for today, in other words information needs to be copied from the open sheet in the source book not from a specific sheet...

and please how can choose the rows and columns to start with, for example if the next blank row now is 183 and data needs to be pasted into column b:e

 

[mark111]

Dear Sir,

hello Dear chihiro,

The code does not work, I am including two workbooks the source book2 and the pasted into book1, Please check it kindly and let me know the right code or you can just use the macro button (trpro) with the right code.

I tried my best extensively to make your code work but with no luck.....




Regards

Mark swan

 

[Chihiro]

I assume that new sheets are added as it goes along, and you want info transferred from newest sheet.

Just minor changes to column reference and sheet reference.

Sub Test()
Dim markWb As Workbook
Dim path As String
Dim lRow As Long

'Change path to your workbook path
path = "C:\Test\Book2.xlsx"
Set markWb = Workbooks.Open(path)

'If not the first sheet, change as needed

markWb.Sheets(markWb.Worksheets.Count).Range("C3:G3").Copy

lRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row + 1

'If data should not be in column A:E, then change column # from 1 to something else
ThisWorkbook.Sheets(1).Cells(lRow, 3).PasteSpecial xlPasteValues

'Save and close source workbook
markWb.Close True

Set markWb = Nothing

End Sub

 

[mark111]

Dear Chihiro,

the code does not runn correctly could you kindly run the code on the files I sent you and send them to me, maybe I can figure out what is going on?

regards

mark Swan

 

[Chihiro]

Give more detail please (Such as error message & the line where it gives error). I tested on your file without issue.

See attached.

 

[mark111]

Dear Chihiro, thanks, however when trying the code with the real file which contains many daily sheets with witch I need to transfer data from each new days's new sheet or activate sheet, the code could worke] with no error message but it only paste in cell c and disregards the rest of cells, also it does not recognize the current or active worksheet open sheet in the source book where the info are copied from.

so there are many functioning problems, event though the real workbook I am modifying the code to, has the exact same parameters as the test books I sent you.

also I tried to build new pages in the test book2 and it started disfunctioning, so the code is not recognizing the above issues.

also I need the source work book to be open as I am doing this operations and do not need to close until I close it maually after finishing my work

maybe it needs modifications accordingly

best regards

Mark swan

 

[mark111]

Dear Chihiro, I wanted to let you know that I got it working after much modifications, so Please do not bother with my last inquiry, just wanted to let you know to save you the trouble...

however I have one problem which I could not resolve which that the vba is not recognizing the next empty row as empty if there is no value in column 3.

in case I do not sell any products in catogary in column 3 the day before, and the cell remains empty, the vba code recognize the whole range C3:G3 as empty and past on top of it, unless there is a value in column 3. so I must make sure that the value is always full with any number in this cell.

is there anyway around this issue so that if I forget to add 0 if there is no sales from the previous day in that catogary for the vba to recognize the row as not empty since it has a value in column 4 or 5 or 6?

best regards

Mark Swan