Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi everybody, following on from my previous post that JRForm was kind enough
to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Mike,
If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Mike,
Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
mke.
This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
mike,
I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
Thanks for your help and thanks for being patient. That's exactly what I need to copy the information to the new sheet, but there are multiple entries of xinst. so how do I get the macro to loop back to row4 to look for the next xinst and then copy to the other sheet after the previous copy... ie. A4, A5, A6, A7 and B4, B5, B6, B7. I've tried duplicating the sheet.activate and cell.select and activecell.offset commands but that isnt working. thanks again mike "JRForm" wrote: mike, I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
mike,
I need to understand this better let recap what I think we have so far. 1. you are looking for values in a certain row then taking the values for 2 rows above and 2 rows below. 2. With the values (r2,r5) go to another sheet and place them there. Okay if that is right then my questions are- *Is the Sheet Name that the values (r2,r5) will be placed the same? *Do you want the values in a column only? *Do you want the values in a row only? "mike" wrote: Hi JR, Thanks for your help and thanks for being patient. That's exactly what I need to copy the information to the new sheet, but there are multiple entries of xinst. so how do I get the macro to loop back to row4 to look for the next xinst and then copy to the other sheet after the previous copy... ie. A4, A5, A6, A7 and B4, B5, B6, B7. I've tried duplicating the sheet.activate and cell.select and activecell.offset commands but that isnt working. thanks again mike "JRForm" wrote: mike, I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub 'dave peterson wrote this code |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
So to clarify, I have a worksheet, the columns are filled from C to n (n being an unknown number of columns) and rows 1 to 44. In row 4 is the institution type, called xinst in my macro. I would like to search row 4 for all columns to n for xinst that the user has specified. when the macro finds xinst in column n row4, i would like it to copy information from rows 2 and 6 in the same column to a new sheet. In the new sheet, the data from col n, row2 will go to col A, row 5 (the starting row) and data from row 6 to Col B row 5. (This is where we are up to now) But I need the macro to carry on searching row 4 for xinst and then copy the next instance of data to the next row in the new sheet, ie. Col A, row6 and Col B, row 6; row 7, row 8, and so on. The sheetname is created earlier on in the macro by an input box and is therefore always referred to as "SheetName". I hope this answered your queries and you can help further. Thanks again, mike "JRForm" wrote: mike, I need to understand this better let recap what I think we have so far. 1. you are looking for values in a certain row then taking the values for 2 rows above and 2 rows below. 2. With the values (r2,r5) go to another sheet and place them there. Okay if that is right then my questions are- *Is the Sheet Name that the values (r2,r5) will be placed the same? *Do you want the values in a column only? *Do you want the values in a row only? "mike" wrote: Hi JR, Thanks for your help and thanks for being patient. That's exactly what I need to copy the information to the new sheet, but there are multiple entries of xinst. so how do I get the macro to loop back to row4 to look for the next xinst and then copy to the other sheet after the previous copy... ie. A4, A5, A6, A7 and B4, B5, B6, B7. I've tried duplicating the sheet.activate and cell.select and activecell.offset commands but that isnt working. thanks again mike "JRForm" wrote: mike, I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate Range("E4:AQ4").Select If ActiveCell < xinst Then Do Until ActiveCell = xinst If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If ActiveCell.Offset(0, 1).Select Loop End If End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) 'dave peterson Dim iCtr As Long Dim mySFX As String Dim myStr As String Do |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Okay mike,
Here is some code to do what you want. I created sample data in a worksheet and set the value for xinst to 5. Paste this code in your code module and create the test data then run the Mike sub. Sub Mike() Dim xinst As String Dim Oldsheet As String Dim i As Integer Dim ShtName As String 'Variable to simulate your code 'r2 and r6 are public variables ShtName = "Sheet2" '"Sheet2" should be Sheetname when in your code Oldsheet = ActiveSheet.Name 'Capture sheet so we can return to it xinst = 5 'The value I used for testing i = 0 Range("E4").Select If ActiveCell < "" Then Do Until ActiveCell = "" If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) i = i + 1 Call PasteMeHere(Oldsheet, i, ShtName) End If ActiveCell.Offset(0, 1).Select Loop End If End Sub Function PasteMeHere(shtOld As String, k As Integer, ShtName As String) Sheets(ShtName).Select 'This will be the 'sheetname' Sheets(ShtName).Range("A4").Select ActiveCell.Offset(k, 0) = r2 ActiveCell.Offset(k, 1) = r6 'return to sheet that call came from and continue Sheets(shtOld).Select End Function "mike" wrote: Hi JR, So to clarify, I have a worksheet, the columns are filled from C to n (n being an unknown number of columns) and rows 1 to 44. In row 4 is the institution type, called xinst in my macro. I would like to search row 4 for all columns to n for xinst that the user has specified. when the macro finds xinst in column n row4, i would like it to copy information from rows 2 and 6 in the same column to a new sheet. In the new sheet, the data from col n, row2 will go to col A, row 5 (the starting row) and data from row 6 to Col B row 5. (This is where we are up to now) But I need the macro to carry on searching row 4 for xinst and then copy the next instance of data to the next row in the new sheet, ie. Col A, row6 and Col B, row 6; row 7, row 8, and so on. The sheetname is created earlier on in the macro by an input box and is therefore always referred to as "SheetName". I hope this answered your queries and you can help further. Thanks again, mike "JRForm" wrote: mike, I need to understand this better let recap what I think we have so far. 1. you are looking for values in a certain row then taking the values for 2 rows above and 2 rows below. 2. With the values (r2,r5) go to another sheet and place them there. Okay if that is right then my questions are- *Is the Sheet Name that the values (r2,r5) will be placed the same? *Do you want the values in a column only? *Do you want the values in a row only? "mike" wrote: Hi JR, Thanks for your help and thanks for being patient. That's exactly what I need to copy the information to the new sheet, but there are multiple entries of xinst. so how do I get the macro to loop back to row4 to look for the next xinst and then copy to the other sheet after the previous copy... ie. A4, A5, A6, A7 and B4, B5, B6, B7. I've tried duplicating the sheet.activate and cell.select and activecell.offset commands but that isnt working. thanks again mike "JRForm" wrote: mike, I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String iprompt1 = "Please enter the name of the country to search." ititle1 = "xcountry" xcountry = InputBox(iprompt1, ititle2) iprompt2 = "Please enter the institution type to search." ititle2 = "xtype" xinst = InputBox(iprompt2, ititle2) mPrompt1 = "Please confirm that you wish to create a " + xinst + " report for " + xcountry mbutton1 = vbYesNo + vbQuestion mTitle1 = "Confirm Report details" repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before writing report. If repconf = vbYes Then 'if the user clicks yes, the macro continues SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on input OkToAdd = False If SheetExists(SheetName) = False Then OkToAdd = True Else 'match upper/lower case of existing sheet name SheetName = Sheets(SheetName).Name oldreport = Range("a1") resp = MsgBox("That report was created on " & oldreport & _ vbLf & "Do you wish to create a second report?", Buttons:=vbOKCancel + vbCritical, Title:="Duplicate reports") If resp = vbCancel Then Exit Sub Else: resp = vbOK OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(SheetName, wks) Range("A1").Value = Date End If Sheets(xcountry).Activate |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy selected cells to new sheet.
Hi JR,
That worked. Thanks for your help. Cheers, mike "JRForm" wrote: Okay mike, Here is some code to do what you want. I created sample data in a worksheet and set the value for xinst to 5. Paste this code in your code module and create the test data then run the Mike sub. Sub Mike() Dim xinst As String Dim Oldsheet As String Dim i As Integer Dim ShtName As String 'Variable to simulate your code 'r2 and r6 are public variables ShtName = "Sheet2" '"Sheet2" should be Sheetname when in your code Oldsheet = ActiveSheet.Name 'Capture sheet so we can return to it xinst = 5 'The value I used for testing i = 0 Range("E4").Select If ActiveCell < "" Then Do Until ActiveCell = "" If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) i = i + 1 Call PasteMeHere(Oldsheet, i, ShtName) End If ActiveCell.Offset(0, 1).Select Loop End If End Sub Function PasteMeHere(shtOld As String, k As Integer, ShtName As String) Sheets(ShtName).Select 'This will be the 'sheetname' Sheets(ShtName).Range("A4").Select ActiveCell.Offset(k, 0) = r2 ActiveCell.Offset(k, 1) = r6 'return to sheet that call came from and continue Sheets(shtOld).Select End Function "mike" wrote: Hi JR, So to clarify, I have a worksheet, the columns are filled from C to n (n being an unknown number of columns) and rows 1 to 44. In row 4 is the institution type, called xinst in my macro. I would like to search row 4 for all columns to n for xinst that the user has specified. when the macro finds xinst in column n row4, i would like it to copy information from rows 2 and 6 in the same column to a new sheet. In the new sheet, the data from col n, row2 will go to col A, row 5 (the starting row) and data from row 6 to Col B row 5. (This is where we are up to now) But I need the macro to carry on searching row 4 for xinst and then copy the next instance of data to the next row in the new sheet, ie. Col A, row6 and Col B, row 6; row 7, row 8, and so on. The sheetname is created earlier on in the macro by an input box and is therefore always referred to as "SheetName". I hope this answered your queries and you can help further. Thanks again, mike "JRForm" wrote: mike, I need to understand this better let recap what I think we have so far. 1. you are looking for values in a certain row then taking the values for 2 rows above and 2 rows below. 2. With the values (r2,r5) go to another sheet and place them there. Okay if that is right then my questions are- *Is the Sheet Name that the values (r2,r5) will be placed the same? *Do you want the values in a column only? *Do you want the values in a row only? "mike" wrote: Hi JR, Thanks for your help and thanks for being patient. That's exactly what I need to copy the information to the new sheet, but there are multiple entries of xinst. so how do I get the macro to loop back to row4 to look for the next xinst and then copy to the other sheet after the previous copy... ie. A4, A5, A6, A7 and B4, B5, B6, B7. I've tried duplicating the sheet.activate and cell.select and activecell.offset commands but that isnt working. thanks again mike "JRForm" wrote: mike, I am a little confused with the your code examples. What I posted before would get the values you need from the activesheet. I did not see in the code where you use the values for r2 and r5. Here is how you can post the values to other sheets/ranges along with the previous code I posted. Range("E4").Select If ActiveCell < xinst Then 'if no match loop to find the cell with xinst Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop 'Found xinst now get the r2, r5 values If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If 'Put the values where needed. Sheets("Sheet2").Range("A3") = r2 Sheets("Sheet2").Range("A7") = r6 I hope this helps "mike" wrote: Hi JR, Thanks, the macro is finding the values but the copy paste function isnt working.. I've tried: If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy_sheets(sheetname).range("A6") r5 = ActiveCell.Offset(1, 0)copy_sheets(sheetname).range("B6") and... If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0).copy r2 =sheets(sheetname).range("A6").paste r5 = ActiveCell.Offset(1, 0)copy r5=sheets(sheetname).range("B6").paste but the macro crashes. I cant see what the problem is... can you help? Thanks Mike "JRForm" wrote: mke. This code will get your values. It replaces the code below these two lines Sheets(xcountry).Activate Range("E4:AQ4").Select I did not see in your code where you will use the r2,r6 data. Did I miss it? If ActiveCell < xinst Then Do Until ActiveCell = xinst ActiveCell.Offset(0, 1).Select Loop If ActiveCell = xinst Then r2 = ActiveCell.Offset(-2, 0) r6 = ActiveCell.Offset(2, 0) End If End If "mike" wrote: Hi JR, I've done as you suggested. Now the problem is that the macro is running and it search row 4 for but goes to the end of the worksheet (IQ - data goes to AQ) and then the macro crashes. Should the copy command come before loop? Thanks mike "JRForm" wrote: Mike, Try creating Public variables to hold the new sheet name and the variables r2,r6 should be global. To do this create a module or use the one you have already created. Go to the top of the module and paste this Public varR2 varR6 as variant Public strSheetName as string "mike" wrote: Hi JR, Thanks for your reply. I would use a user form but I'm not familar with forms in Excel or Access for that matter. The problem now is copying the selected cells to the new sheet. Any suggestions would be greatly appreciated, Cheers, mike "JRForm" wrote: Mike, If your workbook tab are the list of countries available why not use a form and a list box? You could use a combo box for the institutiion for example: Dim WrkSht As Long Dim i As Long WrkSht = Sheets.Count For i = 1 To WrkSht Me.ListBox1.AddItem Sheets(i).Name Me.ComboBox1.AddItem "Institution " & i Next i Your user can choose the country then institution and click a command button to run your code that creates the new sheet. "mike" wrote: Hi everybody, following on from my previous post that JRForm was kind enough to answer, I have another problem. Target: my workbook contains lots of sheets that are named by country. I would like a macro to ask the user which country they want to search and what instiution type, then create a new sheet and paste information from specific cells there. Ie. User activates workbook, clicks button, macro runs... types country, types institution. the macro runs and creates a new sheet called "institution report, country". This sheet is filled in column b with the name of the company which has been taken from row 2 of the country sheet and column c with the insitution, which is the row which the macro will search - row 4, plus column d will be any comments which appear in row 6. I have to far got my code to ask for the country and institution, check for exising sheets and create a new sheet putting the date in a1. But now I'm confused and don't know the best way to continue. Looking forward to your replies, thanks in advance, mike CODE: sub mikescode() this code should ask which country you are searching for, then ask what report you wish to create, 'ie. which institutions, and then search the relevant country sheet and copy the name and institution type plus comment 'to a new sheet called _country_institution_ Report, the report should include the date and automatically open 'the print dialog box to print to the local printer. Dim xcountry As String 'the country you wish to search Dim xinst As String 'the institution type you wish to search for Dim today 'today's date to be included in the report Dim r2 'this is row 2 Dim r6 'this is row 6 Dim SheetName As String Dim TestSht As Object Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim oldreport As String |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selected Range to new sheet | Excel Programming | |||
Copy selected cells | Excel Programming | |||
How can I copy the values of all selected cells to the same cells inanother Sheet | Excel Programming | |||
How to copy selected listbox item to sheet | Excel Programming | |||
copy selected rows to new sheet | Excel Programming |