Consulting

Results 1 to 12 of 12

Thread: copy cells from all sheets to one main sheet

  1. #1
    VBAX Regular
    Joined
    Apr 2022
    Posts
    9
    Location

    copy cells from all sheets to one main sheet

    Hey,
    I want to copy some cells from all sheets in a workbook into one master workbook. I wrote this code:

    HTML Code:
    Dim kopia_nazwa
    Dim praw1
    Dim praw3
    Dim praw5
    Dim praw10    
    
    kopia_nazwa = Range("a1").Value
    Worksheets("data").Range("a3").Value = kopia_nazwa
    
    praw1 = Range("e6").Value
    Worksheets("data").Range("b" & i).Value = praw1
    
    praw3 = Range("f6").Value
    Worksheets("data").Range("c" & i).Value = praw3
    
    praw5 = Range("g6").Value
    Worksheets("data").Range("d" & i).Value = praw5
    
    praw10 = Range("h6").Value
    Worksheets("data").Range("e" & i).Value = praw10
    I found code on the internet that executes the given code for all sheets in a workbook:

    HTML Code:
    Sub Dosomething2()
       
    Dim xSh As Worksheet    
    Application.ScreenUpdating = False       
    For Each xSh In Worksheets        
    xSh.Select        
    Call RunCode2    
    Next      
    Application.ScreenUpdating = True
    
    End Sub

    I thought of combining it with a for loop:

    HTML Code:
    Sub Dosomething2() 
       
    Dim xSh As Worksheet    
    Application.ScreenUpdating = False    
    For i = 3 To 168    
    For Each xSh In Worksheets        
    xSh.Select        
    Call RunCode2    
    Next    
    Next i    
    Application.ScreenUpdating = True
    
    End Sub
    
    
    Sub RunCode2()
    
    Dim kopia_nazwa
    Dim praw1
    Dim praw3
    Dim praw5
    Dim praw10    
    
    kopia_nazwa = Range("a1").Value
    Worksheets("data").Range("a3").Value = kopia_nazwa
    
    praw1 = Range("e6").Value
    Worksheets("data").Range("b" & i).Value = praw1
    
    praw3 = Range("f6").Value
    Worksheets("data").Range("c" & i).Value = praw3
    
    praw5 = Range("g6").Value
    Worksheets("data").Range("d" & i).Value = praw5
    
    praw10 = Range("h6").Value
    Worksheets("data").Range("e" & i).Value = praw10
    
    End Sub

    Why doesn't this work, or how can I do it differently?

  2. #2
    Just explain what you want to achieve.
    Make sure to let us know if all sheets or only some, range address or just single cells etc etc
    Where to paste, which column etc etc.
    Are both workbooks open or does one workbook need to be opened?

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Integrate all worksheets into one and your question vanishes as snow in the sun.

    Never split similar data into several worksheets or files or even directories.
    If you use Excel as a database, treat the data as a database.

  4. #4
    VBAX Regular
    Joined
    Apr 2022
    Posts
    9
    Location
    I want to:
    Copy to the "main" sheet copy data from all other sheets in the workbook.
    The cells I want to copy are A1, E6, F6, G6, H6.
    The cells I want to paste into the "main" worksheet are B & x, C & x, D & x, E & x, F & x. Where x is 1, 2, 3 ... n.


    snb: The workbook I have was created by based on a macro I got, so it is done this way. Thank you for the information.
    Last edited by kared; 04-26-2022 at 02:38 AM.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by kared View Post
    snb: The workbook I have was created by based on a macro I got, so it is done this way.
    Why not simply fix the macro that creates the workbook then?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Kared, your code as you have written it will fail.

    Firstly, please dim your variables as a particular variant. Whilst you know what you want others who look at your code will not necessarily understand the variant type over time, so to help them out try to Dim the variants as a particular type.

    Next, Lines 6 & 7 of your submitted code will result in the value of each sheet's Range ("A1").value, overwriting the value in cell Worksheets(Data). Range ("A3"). value because it doesn't increment beyond Range("A3"). I'm assuming that's not what you had intended. Did you notice that you did not define the value "I"? "I' should be the last row of the column you selected namely "A", "E", "F","G", "H", and then you add "1" to find the next blank row.

    The smarter people here will probably define an array based on "A1", "E6", "F6","G6","H6" of each sheet other than Worksheets("Data"), and then transpose that array to the next blank row per sheet. I await their replies with some interest. Your code as you had written it would never have worked.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Maybe something like the below for starters:
    Sub test()    
        Dim wsMain As Worksheet, ws As Worksheet, var As Variant
        
        Set wsMain = Sheets("Main")
        For Each ws In ThisWorkbook.Sheets
            With ws
                If .Name <> "Main" Then
                    var = Array(.Range("A1"), .Range("E6"), .Range("F6"), .Range("G6"), .Range("H6"))
                    wsMain.Range("B" & wsMain.Range("B" & Rows.Count).End(xlUp).Row + 1).Resize(, UBound(var) + 1) = var
                End If
            End With
        Next ws
    End Sub
    Loops through all worksheets in the workbook (omitting 'Main') takes the required range into an array, places that array in 'Main' starting at the next available row in column B of 'Main'.

    Hope this helps
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Georgiboy...... you are seriously impressing me. If Boris steps down are you available?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Quote Originally Posted by Aussiebear View Post
    Georgiboy...... you are seriously impressing me. If Boris steps down are you available?
    Thanks Aussie,

    I am afraid that job over here is only for fools (as you may see on the news), it seems that the new world order is to have the world run by maniacs & morons.

    I couldn’t party every day like Boris can...
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  10. #10
    VBAX Regular
    Joined
    Apr 2022
    Posts
    9
    Location
    Thank you for your help! Will analyze this code to learn from it.

    Aussiebear: Thank you for your advice.

  11. #11
    VBAX Regular
    Joined
    Apr 2022
    Posts
    9
    Location
    georgiboy: Thank you for your help! Will analyze this code to learn from it.

    Aussiebear: Thank you for your advice.

  12. #12
    Re Post #9 (georgiboy)
    Have not heard a truer statement like that for a long time. Many examples all over the world.
    The only problems is, in the west anyway, who voted them in. What does that say about the intelligence of the voters?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •