Help w/ VBA!

[Shraeder]

Hi - I'm very new to VBA and am trying to create a macro to extract data from one spreadsheet with 6 tabs and insert it into another spreadsheet with 6 tabs but the data doesn't always populate in the correct tab. I was using the record function to create the macro because I'm not fluent yet in VBA.....any help would be greatly appreciated!

Sub Macro21()
'
' Macro21 Macro
'

'
Cells.Select
Sheets("RFIData").Select
Selection.ClearContents
Range("E38").Select
Windows("Procore Test Data 1.xlsx").Activate
Cells.Select
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Range("C23").Select
Sheets("SubmittalData").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Windows("Procore Test Data 1.xlsx").Activate
Range("C28").Select
Sheets("Submittals").Select
Cells.Select
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
ActiveSheet.Paste
Range("C22").Select
Sheets("ObservationData").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Windows("Procore Test Data 1.xlsx").Activate
Range("C24").Select
Sheets("Observations").Select
Cells.Select
Range("A55").Activate
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
ActiveSheet.Paste
Range("C11").Select
Sheets("PunchListData").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Windows("Procore Test Data 1.xlsx").Activate
Range("C89").Select
Sheets("PunchList").Select
Range("A1").Select
Selection.Copy
Cells.Select
Range("A559").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
ActiveSheet.Paste
Range("D24").Select
Sheets("ManpowerData").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Windows("Procore Test Data 1.xlsx").Activate
Range("D588").Select
Sheets("ManpowerLog").Select
Cells.Select
Range("A16").Activate
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
ActiveSheet.Paste
Range("K45").Select
Sheets("InspectionData").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Windows("Procore Test Data 1.xlsx").Activate
Range("E38").Select
Sheets("InspectionItemDetails").Select
Cells.Select
Selection.Copy
Windows("4608 Monthly Staff Report Test Rev3.xlsm").Activate
ActiveSheet.Paste
Range("D25").Select
End Sub

 

[kevin9999]

The reason your data doesn’t always go to the correct sheet is because you’re not being explicit enough in your code. A better approach would be to firstly set the Workbooks to variables like this:

Dim wb1 as Workbook, wb2 as Workbook
Set wb1 = Workbooks("Procore Test Data 1.xlsx")
Set wb2 = Workbooks("4608 Monthly Staff Report Test Rev3.xlsm")

Secondly, you don’t need all the Activate and Select lines when doing a simple copy. As a general rule, you can copy with a single line of code. After setting the variables as above, an example could be:

Wb1.Sheets("Submittals").Cells.Copy wb2.Sheets(“Observations”).Range(“A1”)

Hopefully you get the gist of what I’m explaining above. Try it, and let me know how you go ?

 

[Alex Blakenburg]

You can try this, but make sure you have a taken a copy of your workbooks.
@kevin9999 - I had to activate the sheets to release the highlighted copy/paste areas and reposition the active cell. Let me know if you have another way of doing it

Sub CopySheets()

    Dim wbProc As Workbook
    Dim wbMthly As Workbook
    Dim shtProc As Worksheet
    Dim shtMthly As Worksheet
    Dim rngProc As Range
    Dim arrProc() As String
    Dim arrMthly() As String
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Set wbProc = Workbooks("Procore Test Data 1.xlsx")
    Set wbMthly = Workbooks("4608 Monthly Staff Report Test Rev3.xlsm")

    arrProc = Split("Submittals,Observations,PunchList,ManpowerLog,InspectionItemDetails", ",")
    arrMthly = Split("SubmittalData,ObservationData,PunchListData,ManpowerData,InspectionData", ",")
    
    For i = 0 To UBound(arrProc)
        Set shtProc = wbProc.Worksheets(arrProc(i))
        Set shtMthly = wbMthly.Worksheets(arrMthly(i))
        Set rngProc = shtProc.UsedRange
        
        shtMthly.Cells.ClearContents
        rngProc.Copy Destination:=shtMthly.Range(rngProc.Address)
        Application.CutCopyMode = False
        
        shtProc.Activate
        rngProc.Cells(1, 1).Select
        
        shtMthly.Activate
        shtMthly.Range("A1").Select
    
    Next i

    Application.ScreenUpdating = True

End Sub

 

[kevin9999]

You can try this, but make sure you have a taken a copy of your workbooks.
@kevin9999 - I had to activate the sheets to release the highlighted copy/paste areas and reposition the active cell. Let me know if you have another way of doing it

Sub CopySheets()

    Dim wbProc As Workbook
    Dim wbMthly As Workbook
    Dim shtProc As Worksheet
    Dim shtMthly As Worksheet
    Dim rngProc As Range
    Dim arrProc() As String
    Dim arrMthly() As String
    Dim i As Long
   
    Application.ScreenUpdating = False
   
    Set wbProc = Workbooks("Procore Test Data 1.xlsx")
    Set wbMthly = Workbooks("4608 Monthly Staff Report Test Rev3.xlsm")

    arrProc = Split("Submittals,Observations,PunchList,ManpowerLog,InspectionItemDetails", ",")
    arrMthly = Split("SubmittalData,ObservationData,PunchListData,ManpowerData,InspectionData", ",")
   
    For i = 0 To UBound(arrProc)
        Set shtProc = wbProc.Worksheets(arrProc(i))
        Set shtMthly = wbMthly.Worksheets(arrMthly(i))
        Set rngProc = shtProc.UsedRange
       
        shtMthly.Cells.ClearContents
        rngProc.Copy Destination:=shtMthly.Range(rngProc.Address)
        Application.CutCopyMode = False
       
        shtProc.Activate
        rngProc.Cells(1, 1).Select
       
        shtMthly.Activate
        shtMthly.Range("A1").Select
   
    Next i

    Application.ScreenUpdating = True

End Sub

@Alex Blackenburg - you're absolutely right of course; I merely wanted to demonstrate that it wasn't necessary to use Activate/Select as separate lines of code to do the actual Copy action. Cheers

 

[Shraeder]

Thanks Kevin! Totally appreciate the help! Going to give it a try now and will let you know.