logo
down
shadow

Move row to worksheet with a variable name


Move row to worksheet with a variable name

By : user2954575
Date : November 22 2020, 10:31 AM
Hope that helps Look at this solution. It addresses a few issues and might simplify what you are trying to do, or at least give you some ideas for new ways to approach this.
Some notes:
code :
AddSheetIfMissing (ActiveWorkbook.Sheets("DCM").Cells(i, 1).Value)
For lCol = 1 to lastCol
    Sheets(sheet2).Cells(tRow, lCol) = Sheets(sheet1).Cells(lRow, lCol)
Next lCol
Private Sub AddtoWorksheet()
Dim temp1 As String, temp2 As String
Dim i As Long, x As Long, tRow As Long
Dim lastRow1 As Long, lastRow2 As Long, lastCol As Long
Dim Sheet1 As String, Sheet2 As String, tempSheet As String
Dim isNew As Boolean

'Define your sheet names
Sheet1 = "Master"
Sheet2 = "DCM"

'Get last row for each sheet
lastRow1 = Sheets(Sheet1).Range("A" & Rows.count).End(xlUp).row
lastRow2 = Sheets(Sheet2).Range("A" & Rows.count).End(xlUp).row

For i = 2 To lastRow1
    temp1 = Sheets(Sheet1).Cells(i, 1).Value
    For x = 1 To lastRow2
        temp2 = Sheets(Sheet2).Cells(x, 1).Value
        If temp1 = temp2 Then

'           AddSheetIfMissing (ActiveWorkbook.Sheets("DCM").Cells(i, 1).Value)
            isNew = AddSheetIfMissing(temp1)

            'Grab the last column number from Master sheet
            lastCol = Sheets(Sheet1).Cells(1, Columns.count).End(xlToLeft).column

            'Set the row on the new sheet
            If isNew = True Then
                tRow = 1
            Else
                tRow = Sheets(temp1).Range("A" & Rows.count).End(xlUp).row + 1
            End If

'           ActiveWorkbook.Sheets("Master").Cells(i, 1).EntireRow.Copy _
'               Destination:=Worksheets(" & temp2 & ").Range("A" & Rows.count).End(xlUp).Offset(1)
            For lCol = 1 To lastCol
                Sheets(temp1).Cells(tRow, lCol).Value = Sheets(Sheet1).Cells(i, lCol).Value
            Next lCol
        End If
    Next x
Next i

End Sub
Function AddSheetIfMissing(tempName As String) As Boolean
Dim ws As Worksheet
Dim isNew As Boolean
isNew = False
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(tempName)
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.name = tempName
        isNew = True
    End If
AddSheetIfMissing = isNew
End Function


Share : facebook icon twitter icon
Excel Macro Pt. 1 - Search Worksheet Column for USER generated INPUT and Move Row to new Worksheet

Excel Macro Pt. 1 - Search Worksheet Column for USER generated INPUT and Move Row to new Worksheet


By : natasou
Date : March 29 2020, 07:55 AM
wish of those help Using .Select is your culprit. If you don't select another sheet, you will not have to save your position. See this for an excellent reason why Select is not a good idea.
this entire code block:
code :
 'Select row in Sheet1 to copy
 Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
 Selection.Copy

 'Paste row into Sheet2 in next row
 Sheets("Test").Select
 Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
 ActiveSheet.Paste
 'Copy from row LSearchrow to LCopyToRow in Test sheet
 Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy _
    Sheeets("Test").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow))
Dim NewWS As Workbook
Set NewWS = Workbooks.Add
NewWS.Sheets("Sheet1").Range("A1").Value = 3
NewWS.SaveAs "myFilename.csv", xlCSV
NewWS.Close
Move Row to another worksheet where cell equals worksheet name

Move Row to another worksheet where cell equals worksheet name


By : nelvin
Date : March 29 2020, 07:55 AM
hop of those help? i will use the next:
ActiveCell.EntireRow.Insert 'This insert a new row, in the cases ["Matched rows should always copy to Row 2 of the corresponding worksheet moving existing data down a row"]
code :
Sub Main()
    Sheets("All Data").Activate
    Range("A2").Activate
    Dim SheetToPaste As String
    Do While ActiveCell.Value <> ""
        Select Case ActiveCell.Value
            Case "Hoja2"
                SheetToPaste = "Hoja2"
            Case "Hoja3"
                SheetToPaste = "Hoja3"
            Case Else
                SheetToPaste = "Mismatch"
        End Select
        ActiveCell.EntireRow.Copy
        Sheets(SheetToPaste).Activate
        Range("A2").Activate
        ActiveCell.EntireRow.Insert
        Application.CutCopyMode = False
        Sheets("All Data").Activate
        ActiveCell.EntireRow.Delete
    Loop
End Sub
Can I not use VBA worksheet variable Me to populate a ComboBox embedded in that Excel Worksheet?

Can I not use VBA worksheet variable Me to populate a ComboBox embedded in that Excel Worksheet?


By : Mohit Sharma
Date : March 29 2020, 07:55 AM
I wish this help you Since the Worksheet_Activate event by strict definition makes the worksheet active then I would suppose that the ActiveSheet property should refer unerringly to the correct worksheet.
I would stay away from both the Worksheet .Name property and the Worksheet.Index property as worksheets are commonly moved in the queue and renamed. However, the Worksheet .CodeName property typically remains static although it can be changed with a little effort.
code :
Private Sub Worksheet_Activate()
    With Sheet1.Shapes("TheComboBox").ControlFormat
       .RemoveAllItems
       .List = Array("me", "you")
    End With
End Sub
Move Worksheet to String Variable

Move Worksheet to String Variable


By : Rismanto Juned
Date : March 29 2020, 07:55 AM
wish of those help you can't assign a string like this, if you want to asign the whole Range in Column A, you nned a loop, like this:
code :
For i = 1 To LastRow
    SheetString = SheetString & ";" & Range("A" & i).Value
Next i
VBA Copy and Paste Between Template Worksheet and Variable Worksheet

VBA Copy and Paste Between Template Worksheet and Variable Worksheet


By : Dipen
Date : March 29 2020, 07:55 AM
seems to work fine Your code is fairly close but there are a few issues. First, don't create a variable with its type as the name. Change Dim workBook As Workbook to something like Dim bk As Workbook.
Next you have the statement Set templateFile = "20161115 SMARTnet Template.xlsx". This is effectively trying to set a workbook to a string. Instead change this to Set templateFile = Workbooks("20161115 SMARTnet Template"). Or better yet change this to:
code :
Dim tempStr as String
tempStr = "20161115 SMARTnet Template"
Set templatefile = Workbooks(tempStr)
Sub Macro3()

    Dim templatefile As Workbook
    Dim workingSheet As Worksheet ' only sheet in CSCC Quote
    Dim tempSheet As Worksheet ' created sheet in CSCC Quote to hold temp data
    Dim tempStr As String

    Application.ScreenUpdating = False

    ' Set template file name
    tempStr = "20161115 SMARTnet Template"
    Set templatefile = Workbooks(tempStr)
    Set workingSheet = ActiveSheet
    Set tempSheet = Worksheets.Add(after:=ActiveSheet)

    tempSheet.Name = "neededInfo"

    workingSheet.Range("A1:B4").Copy

    tempSheet.Range("A5").PasteSpecial xlPasteValues

    Application.ScreenUpdating = True

End Sub
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org