PDA

View Full Version : VBA code to copy differing range of cells depending on sheet name



mattymatt35
12-02-2015, 03:56 PM
Hi,

I have an excel sheet where the user fills out the fields in yellow and these are pulled into a COPY SHEET which pulls the cells all into one sentence. I have created a really simple VBA (as I am a novice!) which when the "Create Freetext" button is pressed, it copies the relevant line from the copy sheet to the clipboard so it can be pasted into a non-microsoft system.

The VBA works fine for the first worksheet (INPUT SHEET) but I have four more identical sheets that I need to somehow tell the VBA where to copy the text from depending on which sheet is selected. So for instance, if the user clicks the Create Freetext Button whilst in the INPUT SHEET worksheet, I need Excel to copy cell A7 from the COPY SHEET. However, if the user is in the NewSheet1 worksheet, I need it to copy cell A10 from the COPY SHEET. How do I change the VBA according to the sheet name so that it copies the correct cells?

The sheets I have and the cells I need to copy are:

"INPUT SHEET" needs to copy cell A7
"NewSheet1" needs to copy cell A10
"NewSheet2" needs to copy cell A13
"NewSheet3" needs to copy cell A16
"Newsheet4" needs to copy cell A19

The macro I have (which works for the INPUT SHEET) is:

Sub CopyData()
Application.ScreenUpdating = False
Sheets("COPY SHEET").Visible = True
Sheets("COPY SHEET").Select
Range("A7").Select
Selection.Copy
Sheets("COPY SHEET").Visible = False
Sheets("INPUT SHEET").Select
MsgBox "Your entry has been copied to the clipboard." & Chr(10) & Chr(10) & "Now access your system and click CTRL + V to paste your entry .", vbOKOnly, "Entry Copied Successfully!"
End Sub


I hope this makes sense. - I also need to advise that the COPY SHEET is a hidden sheet.

I admit I not very good at VBA so any help would be really appreciated many thanks.

mattymatt35
12-02-2015, 03:57 PM
I forgot to add the sheet. Here it is.

Thanks

jolivanes
12-03-2015, 12:48 AM
This should be a start.
Note: Change all references, Sheets and Ranges, as required.



Sub Try()
Select Case ActiveSheet.Name
Case Is = "NewSheet1" '<---- If this is the Active Sheet it will do as per following 2 lines
Sheets("INPUT SHEET").Range("A29").Copy Sheets("NewSheet1").Range("A22")
Sheets("INPUT SHEET").Range("A29").Copy Sheets("NewSheet1").Range("A23")
Case Is = "NewSheet2" '<---- If this is the Active Sheet it will do as per following 2 lines
Sheets("INPUT SHEET").Range("A30").Copy Sheets("NewSheet1").Range("A25")
Sheets("INPUT SHEET").Range("A30").Copy Sheets("NewSheet1").Range("A26")
Case Is = "NewSheet3" '<---- If this is the Active Sheet it will do as per following 1 line
Sheets("INPUT SHEET").Range("A31").Copy Sheets("NewSheet1").Range("A28")
End Select
End Sub