VBA to Copy Data from a Variable Sheet to the Current Sheet

[DRMOE]

Hello- I am a complete VBA beginner....however, I am attempting to write a macro to copy data from a source sheet to the current active destination sheet (same workbook).

- The source sheet will vary. I created a drop down list of sheet names in coordinate "Q5". I'd like the macro to refer to this cell in order to identify the source sheet.
- This macro needs to be able to work in multiple destination sheets within the same workbook. For example, tabs 1- 15 are the same format. I may want to use this macro in tab 15 to copy data from tab 2.

For simplicity in this example, I'm copying data from Cells B5:B10 from the source and pasting them in the same range: Cells B5:B10 in the destination.

Anyone have any advice? Thank you in advance!

DMO

 

[Logit]

.

Option Explicit


Sub CpyNPste()


'// CHANGE RANGE AS NEEDED \\
Worksheets("" & Range("C2").Value & "").Range("B5:B10").Copy _
    Destination:=ActiveSheet.Range("B5")




End Sub

Download workbook : https://www.amazon.com/clouddrive/share/dF1NCBdUSpDOLTOhtroAgFbCPBUiSik8eGuJq3yuORm

 

[DRMOE]

Thank Logit! That covered it.

As I continue to try and learn more about this....Is there a way to name the variable sheet name for reuse in a longer/more distinct process? Ie.,

DIM ws = Worksheets("" & Range("q5").Value & "")

ws.Range("B26:B40").Copy _ Destination:=ActiveSheet.Range("B26")

ws.Range("D26:D99").Copy _ Destination:=ActiveSheet.Range("D26")


End Sub


Once again - thank you!!

 

[Logit]

.
The way you have the quasi-code written it appears you are wanting the code to decide
which sheet you are copying from without telling it which sheet it is. That is not possible.

The closest you could get to that scenario would be to use ACTIVESHEET as the 'copy from'
sheet and then you would need to specifically name the sheet the data will be pasted to. That
is the opposite of what the macro is doing now.

Thinking out loud here ... You could create a MENU SHEET or MASTER SHEET from which
everything is decided which sheet to copy from and which sheet to copy to.

You could create two drop downs ... one would list the sheets in the workbook from which
you could choose a single sheet to copy from. The other drop down would be the list of
all sheets in the workbook to which the copied data would be pasted to. The key to this
scenario would be all sheets need to be designed in the same format so you would always
be copying and pasting to the same cells no matter which sheet you selected from the
drop downs.

 

[DRMOE]

Thanks again - it works great! I appreciate your help! This is what I ended up doing:

I put a drop down list on each destination sheet, identifying other sheets to copy from. I then repeated the code that you shared. For example:

Sub CopyData()


' CopyData Macro


Worksheets("" & Range("q5").Value & "").Range("C7:F11").Copy _
Destination:=ActiveSheet.Range("C7:F11")


Worksheets("" & Range("q5").Value & "").Range("B20:B22").Copy _
Destination:=ActiveSheet.Range("B20:B22")


Worksheets("" & Range("q5").Value & "").Range("B26:B40").Copy _
Destination:=ActiveSheet.Range("B26:B40")


Worksheets("" & Range("q5").Value & "").Range("B44:B47").Copy _
Destination:=ActiveSheet.Range("B44:B47")


Worksheets("" & Range("q5").Value & "").Range("e16:f16").Copy _
Destination:=ActiveSheet.Range("e16:f16")


[REMOVED THE EXTRA 30 ROWS OF DETAIL - YOU GET THE POINT]


ActiveSheet.Range("C7").Select



End Sub

 

[Logit]

Glad to help.