I'm too new to VBA to know how to approach this. My code works, but I think it's sloppy and I'm looking for a hand to save or copy each cell that I need in my original workbook and pasting it in specific different cells in my 2nd workbook. My way has the active workbook bouncing back and forth but there has to be a way to get it all in a single transfer which should improve speed and resources. I didn't include the rest of my code after this where I have an "Else" with other stuff, but this is where my problem lies. Any advice or help would be appreciated and you can assume I don't know much if you want to be really clear for me with you help I'd be really grateful!
Dim InputFile As Workbook Dim OutputFile As Workbook If ActiveSheet.Shapes("CommandButton1").ControlFormat.Enabled = False Then If Range("B8") = "ED 3-20" Then Application.ScreenUpdating = False '## Open both workbooks first: Set InputFile = ActiveWorkbook Set OutputFile = Workbooks.Open(Environ("USERPROFILE") & "\OneDrive\GL GEOTECH Inc Gradation Analysis\GL GEOTECH Inc Summary Files Originals\GL GEOTECH - QUALITY SUMMARY (2-20 and 3-20) - Scorpion Construction LTD.xlsm") InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("E16:E36").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("F13").PasteSpecial Paste:=xlPasteValues, Transpose:=True InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("E8").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("S13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("E7").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("R13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("B9").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("C13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("E4").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("E13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("B10").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("D13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("B7").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("T13").PasteSpecial Paste:=xlPasteValues InputFile.Sheets("GL GEOTECH ").Activate InputFile.Sheets("GL GEOTECH ").Range("E5").Copy OutputFile.Sheets("Summary").Activate OutputFile.Sheets("Summary").Range("B13").PasteSpecial Paste:=xlPasteValues OutputFile.Sheets("Summary").Rows(13).Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'Close InputFile & OutputFile: OutputFile.Save OutputFile.Saved = True OutputFile.Close Application.ScreenUpdating = True Beep MsgBox "Data successfully added to summary file."
Bookmarks