Posts by wesleywuyu

Hi

I am new to VBA. Got a quick question for transfer data from several sheets to a master data sheet. The problem I am having now is there are couple blank cells. My code just ignore the blank cells on the master data sheet and paste next to the last cell with content. For example I attached a test excel. with my code. I want to leave the blank cell as it is to keep the same product in one row. below is my code. Thanks ahead! Waiting online!!!!!

Sub Copypaste()
'
' Macro8 Macro
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        ws.Range("B1").Copy
        Worksheets("master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        ws.Range("F1").Copy
        Worksheets("master").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    End If
Next ws
End Sub

Re: Cut content from one sheet to another loop

Quote from pike;655585

hi
this will be the standard code but may need ajustment

Code

Option Explicit
Sub test()
    Dim ws     As Worksheet
    Dim ws1    As Worksheet
    Dim LookInR As Range
    Dim fAddress
    Dim foundone As Range
    Set ws1 = Worksheets("Master")
    For Each ws In Worksheets
        If ws.Name <> ws1.Name Then
            Set LookInR = ws.Range(ws.Range("A1"), ws.Range("A" & ws.Rows.Count).End(xlUp))
            With LookInR
                Set foundone = .Find(what:="API", LookAt:=xlPart, MatchCase:=True)
                If Not foundone Is Nothing Then
                    fAddress = foundone.Address
                    Do
                        foundone.CurrentRegion.Copy Destination:=ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Set foundone = .FindNext(After:=foundone)
                    Loop While foundone.Address <> fAddress
                End If
            End With
        End If
    Next ws
End Sub

Display More

Thanks Pike!! Thats very helpful!!!!

Re: Cut content from one sheet to another loop

Hi Pike,

Thanks again for your help~

I will unmerge cells. But the problem is for the second steps, since the format might be change a little bit due the the original owner randomly added rows for more information. For this situation. Will that be a solution for auto transit info. from each product to a master data sheet?

thanks

Wes

Re: Cut content from one sheet to another loop

Quote from pike;655507

Hi wesleywuyu
Welcome to ozgrid
It a very common request and an example workbook will make testing easier

Hi pike I really appreciate your help. FYI, I attached a file which has a very similar format as the real doc, instead the really one has much more tables. Each table has all information for one product. I basically want each table locate in each table. Also consolidate the information from each table to a master sheet. Eg. rows represent product and column represent represent all criteria. I am not sure if that is possible since all the tables have very similar information but not exactly same. So the format may change by product.

I dont know if I explain clearly. But this is a great help for me!!!! Thanks so much for your help and advice! forum.ozgrid.com/index.php?attachment/52343/

Hey

First, I dont have any coding background, Maybe my question is very stupid. I am working on a project and need a coding. What I am trying to do is

Select the sheets, and using while loop to go through all the rows

if find the "API" cell in column A, and start counting till to the first empty row, then copy these rows to a new sheet

after this one, keep going in the loop, and find the next 'API', and start the new counter again. rest are same as the pervious step.

Waiting online! Thanks a lot!