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.
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.