use VBA to copy from multiple spreadsheets

[niffer_88]

I am looking to use a macro to copy and paste information stored across multiple spreadsheets, in the same work book in to a specific sheet in specific locations on that sheet. This will then need to move on to another sheet into the workbook and copy and paste a different array of information from the multiple spreadsheets.

I am ok in VBA once I get started but im not great as starting to write code

 

[My Aswer Is This]

Yes this can be done. But we need exact details. Copy data from where and paste it where.
We need to know the name of the sheet where we post the data to. Will we be copying data from all the sheets in the workbook?

For example:

Copy data in Range("A1:A10") from all sheets in workbook except for sheet named "Master"
And paste this data into column "B" of sheet named "Master"

 

[mattmickle]

If it's something you do manually, just record your keystrokes as you're doing it. The macro will write itself.

 

[niffer_88]

Heya, I am not looking to copy from every sheet but a few out the workbook, there are more sheets for the information that is being copied to go into.

so for example I need to copy the information into a sheet called 'Restaurant wall', the information I need to copy this from are in sheets called Cookshop, Textiles, Bathshop, Home Org, Lighting and Home Dec, the range of data for each of these sheets is A8:M17, I then need this information to go in the 'Resturant wall' sheet in specific locations so

Cookshop in to A7

Textiles into A33

Bathshop into A54

Home Org into A76

Lighting into A99

Home dec into A120


Then I need to repeat the process so that I get information from the 'cookshop etc' sheets in to around 8 other sheets.

apologies about the delay

thanks

 

[Fluff]

Try

Sub Copydetails()

    Dim ShtArr As Variant
    Dim Ws As Worksheet
    Dim Sht As Variant

    ShtArr = Array("Cookshop", "Textiles", "Bathshop", "Home Org", "Lighting", "Home Dec")
    
    With Sheets("Restaurant wall")
        For Each Sht In ShtArr
            Set Ws = Sheets(Sht)
            Select Case Ws.Name
                Case "Cookshop"
                    Ws.Range("A8:M17").Copy .Range("A7")
                Case "Textiles"
                    Ws.Range("A8:M17").Copy .Range("A33")
                Case "Bathshop"
                    Ws.Range("A8:M17").Copy .Range("A54")
            End Select
        Next Sht
    End With
    
End Sub

You'll need to add the rest of the Cases