Posts by mic

Hi Joelsmally,

I think the draft below may be able to provide a clearer picture of my problem.

Below is the English version code that may make my question clearer:

Open file AA
Search from column J8 to Z8
If found a value in J8
copy the cell
open a new workbook and name it BB
go to column C4 in BB to paste the value
go back to AA and copy J6
go to BB and paste into A4
go to AA and copy G8
go to BB and paste in B4
go to AA and copy F8
go to BB and paste in E4
go to AA and copy D8
go to BB and paste in L4
Else
go to the next cell (K8)

If found a value in k8
copy the cell
avtivate BB
go to column C5 in BB to paste the value
go back to AA and copy k6
go to BB and paste into A5
go to AA and copy G8
go to BB and paste in B5
go to AA and copy F8
go to BB and paste in E5
go to AA and copy D8
go to BB and paste in L5

repeat the same process until Z8
Go to the next row in AA
repeat the searching and pasting from J9 to Z9
Save BB
Close AA

As you can see, I need to copy from BB horizontally and paste in AA vertically. If I can find 5 values in BB, AA will require 5 rows to reflect each value.

Thanks very much for your help!

Hi Joelsmally,

Yup, I create the 'stock compile' manually from stock. Reason is that I do not know how to create loop to check the first row in a range before going to the second row.

The codes below only check for the first cell that has number on it but it does not proceed further.

Sub GetBlankCell()
Dim myRange As Range, cell As Range
Set myRange = ActiveSheet.Range("C121:C141")
For Each cell In myRange
If cell > "0" Then
MsgBox cell.Value
Range("c4") = cell
Exit For
End If
Next cell
End Sub

How can I improve this code further so that it will check all the cells within the range of (C121:C141), which is in row 8, copy each individual cell into another workbook under selected columns and proceed the next range in row 9?

Sorry for the trouble but I believe this is the one that I have problem with.

Thanks!

Here is the other file for your guidance.

Thanks

Hi Dave and joelsmally,

Thank you very much for your reply. I appreciate it very much.

Maybe my question does not reflect my real problem.

I have attached 2 files for your guidance. The file "stock compile" copies and pastes the information from "stock". As you can see, the horizontal information in "stock" is right now in the vertical situation, and the delivery date is duplicated according to the number of stock we have in a row. For example, in "stock" on 01-05, we have 3 cargoes, namely DD,EE & GG. These information is inserted row by row and thus duplication of the date is required.

The location and customer information is also being searched in each row and inserted in the specified columns in "stock compile".

One good thing is that the template does not change and the rows and column's location can be fixed.

I am thinking of using loops that changes the row numbers and column numbers.

Is it possible to do that?

Thank you very much again for your help.

Hi all,

I have several spreadsheets that have data that scattered all over the worksheet. I need a macro that can look into each cell in each worksheet, copy the value if there's any and paste it into a new worksheet under selected columns.

I am stuck with trying to create loops that can look into each cell and copy the value.

Can any kind soul offer any help?

Thanks