Hi all,
I am in the final stages of a spreadsheet whereby I am trying (and largely succeeded) in automating a lot of the copy/paste at the click of a button type stuff to move my work to different tabs.
Through a lot of self learning, and a bit of help here and there from the amazing people on here, I have pretty much managed to get where I want it to be…..
However, as is always the case when it comes to stress testing it to see what bugs/issues I have, I have come across an issue I am struggling to amend and I am hoping it is just my lack of VBA ability that’s just causing what appears fixable but highly frustrating at the same time until I can do so…..
So, to give a picture, I have 4 tabs where I have jobs waiting to be allocated. Each tab relates to work in a different town.
I input information as it comes in and then click on a button I put at the top to transfer cells over to another tab (allocated jobs) when I have managed to allocate a job (all 4 town tabs feed into this one allocated jobs worksheet). At the same time, where a job has not been allocated but has paperwork to be done (Forms A and B), they also transfer the same data to the paperwork tab (regardless of whether a job has a persons allocated or not).
Again, at the click of a button on each town worksheet, it feeds into the paperwork tab just like allocated jobs.
The issue I am now having (and can’t work out why) is that when I click the button to move information from one town tab to the allocated jobs & paperwork tab - which is fine, but the next town tab, I click and it transfers over the information but actually overwrites the anything already in the paperwork tab.
Please see below the code I use for the three towns (code is the same just with the minor alteration of Town 1, Town 2 etc).
I would be very grateful if someone can help me get to the bottom of my errors.
Ultimately, my end goal is to transfer information from Row B downwards on each town tab (based on certain info when it comes to the paperwork element) to two worksheets within the same workbook when I click the button I have linked the code to but want it to input the information on the next available line and avoid any overwriting.
So near yet so far!!!
Many thanks in advance.
Town 1
Town 2
Town 3, 4 etc are the same as the above so it would just be duplication at this point.
I am in the final stages of a spreadsheet whereby I am trying (and largely succeeded) in automating a lot of the copy/paste at the click of a button type stuff to move my work to different tabs.
Through a lot of self learning, and a bit of help here and there from the amazing people on here, I have pretty much managed to get where I want it to be…..
However, as is always the case when it comes to stress testing it to see what bugs/issues I have, I have come across an issue I am struggling to amend and I am hoping it is just my lack of VBA ability that’s just causing what appears fixable but highly frustrating at the same time until I can do so…..
So, to give a picture, I have 4 tabs where I have jobs waiting to be allocated. Each tab relates to work in a different town.
I input information as it comes in and then click on a button I put at the top to transfer cells over to another tab (allocated jobs) when I have managed to allocate a job (all 4 town tabs feed into this one allocated jobs worksheet). At the same time, where a job has not been allocated but has paperwork to be done (Forms A and B), they also transfer the same data to the paperwork tab (regardless of whether a job has a persons allocated or not).
Again, at the click of a button on each town worksheet, it feeds into the paperwork tab just like allocated jobs.
The issue I am now having (and can’t work out why) is that when I click the button to move information from one town tab to the allocated jobs & paperwork tab - which is fine, but the next town tab, I click and it transfers over the information but actually overwrites the anything already in the paperwork tab.
Please see below the code I use for the three towns (code is the same just with the minor alteration of Town 1, Town 2 etc).
I would be very grateful if someone can help me get to the bottom of my errors.
Ultimately, my end goal is to transfer information from Row B downwards on each town tab (based on certain info when it comes to the paperwork element) to two worksheets within the same workbook when I click the button I have linked the code to but want it to input the information on the next available line and avoid any overwriting.
So near yet so far!!!
Many thanks in advance.
Town 1
VBA Code:
Sub Button2_Click()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim iSourceLastRow As Long
Dim iTargetLastRow As Long
'Set variables for source and destination sheets
Set wsSource = Worksheets("Town 1")
Set wsTarget = Worksheets("Allocated Job")
'Find last used row in the source sheet based on data in column A
iSourceLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
'Find first blank row in the destination sheet based on data in column A
'Offset property is to move the copied data 1 row down
iTargetLastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1).Row
'Copy data from the source and Paste in the destination
For Each cel In wsSource.Range("B3:B" & iSourceLastRow)
If Not IsEmpty(cel) Then
cel.Offset(, -1).Resize(1, 17).Copy wsTarget.Range("A" & iTargetLastRow)
iTargetLastRow = iTargetLastRow + 1
End If
Next cel
'Declare variables
Dim sheetNo1 As Worksheet
Dim sheetNo2 As Worksheet
Dim FinalRow As Long
Dim Cell As Range
'Set variables
Set sheetNo1 = Sheets("Town 1")
Set sheetNo2 = Sheets("Paperwork")
'Type a command to select the entire row
Selection.EntireRow.Select
' Define destination sheets to move row
FinalRow1 = sheetNo1.Range("A" & sheetNo1.Rows.Count).End(xlUp).Row
FinalRow2 = sheetNo2.Range("A" & sheetNo2.Rows.Count).End(xlUp).Row
With sheetNo1
'Apply loop for column P until last cell with value
For Each Cell In .Range("P3:P" & .Cells(.Rows.Count, "P").End(xlUp).Row)
'Apply condition to match the "Form A” value
If Cell.Value = "Form A" Then
'Command to Copy and move to a destination Sheet "Paperwork"
.Cells(Cell.Row, "A").Resize(, 16).Copy Destination:=sheetNo2.Cells(FinalRow2 + 1, "A")
FinalRow2 = FinalRow2 + 1
'Apply condition to match the "Form B" value
ElseIf Cell.Value = "Form B" Then
'Command to Copy and move to a destination Sheet "Paperwork"
.Cells(Cell.Row, "A").Resize(, 16).Copy Destination:=sheetNo2.Cells(FinalRow2 + 1, "A")
FinalRow2 = FinalRow2 + 1
MsgBox ("Visits have been transferred to Paperwork and Allocated Job")
End If
Next Cell
End With
End SubTown 2
VBA Code:
Sub Button2_Click()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim iSourceLastRow As Long
Dim iTargetLastRow As Long
'Set variables for source and destination sheets
Set wsSource = Worksheets("Town 2")
Set wsTarget = Worksheets("Allocated Job")
'Find last used row in the source sheet based on data in column A
iSourceLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
'Find first blank row in the destination sheet based on data in column A
'Offset property is to move the copied data 1 row down
iTargetLastRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1).Row
'Copy data from the source and Paste in the destination
For Each cel In wsSource.Range("B3:B" & iSourceLastRow)
If Not IsEmpty(cel) Then
cel.Offset(, -1).Resize(1, 17).Copy wsTarget.Range("A" & iTargetLastRow)
iTargetLastRow = iTargetLastRow + 1
End If
Next cel
'Declare variables
Dim sheetNo1 As Worksheet
Dim sheetNo2 As Worksheet
Dim FinalRow As Long
Dim Cell As Range
'Set variables
Set sheetNo1 = Sheets("Town 2")
Set sheetNo2 = Sheets("Paperwork")
'Type a command to select the entire row
Selection.EntireRow.Select
' Define destination sheets to move row
FinalRow1 = sheetNo1.Range("A" & sheetNo1.Rows.Count).End(xlUp).Row
FinalRow2 = sheetNo2.Range("A" & sheetNo2.Rows.Count).End(xlUp).Row
With sheetNo1
'Apply loop for column P until last cell with value
For Each Cell In .Range("P3:P" & .Cells(.Rows.Count, "P").End(xlUp).Row)
'Apply condition to match the "Form A” value
If Cell.Value = "Form A" Then
'Command to Copy and move to a destination Sheet "Paperwork"
.Cells(Cell.Row, "A").Resize(, 16).Copy Destination:=sheetNo2.Cells(FinalRow2 + 1, "A")
FinalRow2 = FinalRow2 + 1
'Apply condition to match the "Form B" value
ElseIf Cell.Value = "Form B" Then
'Command to Copy and move to a destination Sheet "Paperwork"
.Cells(Cell.Row, "A").Resize(, 16).Copy Destination:=sheetNo2.Cells(FinalRow2 + 1, "A")
FinalRow2 = FinalRow2 + 1
MsgBox ("Visits have been transferred to Paperwork and Allocated Job")
End If
Next Cell
End With
End SubTown 3, 4 etc are the same as the above so it would just be duplication at this point.
