Macro help needed

dhaynes

New Member
Joined
Mar 13, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I having trouble writing a macro to take the first table below from a tab in one Excel file and past it into the last empty row in another file. Any guidance would be much apprecaited

This is the data that current exists in a seperate file
NameTypeTotalPrice
1​
0.704.14$ 101.00
2​
0.714.15$ 102.00
3​
0.724.16$ 103.00

I am trying to copy only the "Type" column (and change it from vertical to hortizontal) into a tab in a seperate file but the trick is that I need to copy this data into the next row which contains no data. Effectively, the goal is to add a new row of data everytime the macro is run.

Type0.700.710.72


Thank you Excel gurus!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi. Without seeing specifics, I put this together which was able to do as you described. Let me know how it works out for you.

VBA Code:
Sub copyover()

Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim eWB As Workbook: Set eWB = Workbooks("ExternalBook.xlsx") 'Identify the external workbook's name here
                                                              'This workbook should also be open
Dim tWS As Worksheet: Set tWS = tWB.Sheets("copyto") 'Update sheetname to copy data to
Dim eWS As Worksheet: Set eWS = eWB.Sheets("Sheet1") 'Update sheet name to copy from

'Finds the last row in column A of the copy to worksheet
'Update column to suit
Dim tLROW As Long: tLROW = tWS.Range("A" & tWS.Rows.Count).End(xlUp).Row

'The below assumes that Type is in column B and the data begins on row 2
'Update column and first row to suit
eWS.Range("B2:B" & Range("B2").SpecialCells(xlCellTypeLastCell).Row).Copy

With tWS
    .Activate
    'Transpose paste to last row, first column of "copyto" sheet and
    .Cells(tLROW + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End With

End Sub
 
Upvote 0
Hi. Without seeing specifics, I put this together which was able to do as you described. Let me know how it works out for you.

VBA Code:
Sub copyover()

Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim eWB As Workbook: Set eWB = Workbooks("ExternalBook.xlsx") 'Identify the external workbook's name here
                                                              'This workbook should also be open
Dim tWS As Worksheet: Set tWS = tWB.Sheets("copyto") 'Update sheetname to copy data to
Dim eWS As Worksheet: Set eWS = eWB.Sheets("Sheet1") 'Update sheet name to copy from

'Finds the last row in column A of the copy to worksheet
'Update column to suit
Dim tLROW As Long: tLROW = tWS.Range("A" & tWS.Rows.Count).End(xlUp).Row

'The below assumes that Type is in column B and the data begins on row 2
'Update column and first row to suit
eWS.Range("B2:B" & Range("B2").SpecialCells(xlCellTypeLastCell).Row).Copy

With tWS
    .Activate
    'Transpose paste to last row, first column of "copyto" sheet and
    .Cells(tLROW + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End With

End Sub
Thank you sir! I amended the code just a bit to the following:

Sub Transfer_bid_data()

Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim eWB As Workbook: Set eWB = Workbooks("OtherWorkbook")

Dim tWS As Worksheet: Set tWS = tWB.Sheets("Bids")
Dim eWS As Worksheet: Set eWS = eWB.Sheets("Auction Results")
Dim eLROW As Long: eLROW = eWS.Range("A" & eWS.Rows.Count).End(xlUp).Row
tWS.Range("B44:B63" & Range("B44:B63").SpecialCells(xlCellTypeLastCell)).Copy

With eWS
.Activate
'Transpose paste to last row, first column of "copyto" sheet and
.Cells(tLROW + 2, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With

End Sub


Your code correctly does what I wanted with the exception of the following (and you may have had this and I messed it up)...Everytime I run the macro, I want it identify the last row with data in it and pasta a new series of data in the next empty row. (i.e.- starting in column E and right below the previous row of data that was added the 1st time the macro was run). I am attempting to keep a historical record of auction results by bidder. Any ideas on this? Thank you!
 
Upvote 0
Thank you sir! I amended the code just a bit to the following:

Sub Transfer_bid_data()

Dim tWB As Workbook: Set tWB = ThisWorkbook
Dim eWB As Workbook: Set eWB = Workbooks("OtherWorkbook")

Dim tWS As Worksheet: Set tWS = tWB.Sheets("Bids")
Dim eWS As Worksheet: Set eWS = eWB.Sheets("Auction Results")
Dim eLROW As Long: eLROW = eWS.Range("A" & eWS.Rows.Count).End(xlUp).Row
tWS.Range("B44:B63" & Range("B44:B63").SpecialCells(xlCellTypeLastCell)).Copy

With eWS
.Activate
'Transpose paste to last row, first column of "copyto" sheet and
.Cells(tLROW + 2, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With

End Sub


Your code correctly does what I wanted with the exception of the following (and you may have had this and I messed it up)...Everytime I run the macro, I want it identify the last row with data in it and pasta a new series of data in the next empty row. (i.e.- starting in column E and right below the previous row of data that was added the 1st time the macro was run). I am attempting to keep a historical record of auction results by bidder. Any ideas on this? Thank you!

Hi. A few questions:
  1. In your updated code, you have tWS.Range("B44:B63" & Range("B44:B63").SpecialCells(xlCellTypeLastCell)).Copy. That won't work and I can explain why later, but more importantly, it also looks like you want to start copy the data from rows 44 through 63. Is that correct? I'm assuming it won't always begin on row 44 to 63, so is there something that can be used to identify this range of rows each time (i.e.; to create the range variable)? For example, is there maybe a column with dates or some other specific information that groups the data in rows 44 and 63 together in some way?
  2. To confirm, you want data from column B44:B63 to be copied from the "Bids" worksheet and pasted to the first open row of data in column E of the "Auction Results" worksheet. Is that correct?
This should be easy enough to get updated for you - just want to ensure I'm following correctly.
 
Upvote 0
Thank you for your response. The data that I want to copy will always be inside of the $B$44:$B$63 range. The data in this range is where the bidding results from the auction will be manually entered. The goal is keep a historical record of this data by copying whatever is in these cells to a new row in a seperate file everytime the macro is run. The column next to this data will contain the name of the person who is bidding. This list of people, or at least the number of people, is not expected to change

With regard to the 2nd question, the data in the $B$44:B$63 is meant to copied into the next empty row starting in column E of the "Auction Results" worksheet. For example, the first time the macro is run, it will paste the data in the range into the first row but the next time the macro is run, it will not write over the data in row 1 but rather paste the data into row 2 and so on.

Thank you very much for your help with this.
 
Upvote 0
Thank you for your response. The data that I want to copy will always be inside of the $B$44:$B$63 range. The data in this range is where the bidding results from the auction will be manually entered. The goal is keep a historical record of this data by copying whatever is in these cells to a new row in a seperate file everytime the macro is run. The column next to this data will contain the name of the person who is bidding. This list of people, or at least the number of people, is not expected to change

With regard to the 2nd question, the data in the $B$44:B$63 is meant to copied into the next empty row starting in column E of the "Auction Results" worksheet. For example, the first time the macro is run, it will paste the data in the range into the first row but the next time the macro is run, it will not write over the data in row 1 but rather paste the data into row 2 and so on.

Thank you very much for your help with this.

Okay, try this. This assumes that the code is placed in the workbook with the Bids worksheet. If that's not correct, then the ThisWorkbook variable needs to be revised.

VBA Code:
Sub Transfer_bid_data()

'declares ThisWorkbook as a variable
Dim tWB As Workbook: Set tWB = ThisWorkbook

'declares this workbook's sheet with the Bid Results
Dim tWS As Worksheet: Set tWS = tWB.Sheets("Bids")

'declares the external workbook
Dim eWB As Workbook: Set eWB = Workbooks("OtherWorkbook")

'declares external workbook's sheet to paste results to (column E)
Dim eWS As Worksheet: Set eWS = eWB.Sheets("Auction Results")

'Determines the last row in column E of the Auction Results tab
Dim eLROW As Long: eLROW = eWS.Range("E" & eWS.Rows.Count).End(xlUp).Row

'Copies bid results from the Bids worksheet
tWS.Range("$B$44:$B$63").Copy

With eWS
          'activates the Auction Results tab
          .Activate
          'Pastes data from Bids worksheet to first open row in column E (eLROW) of the Auction Results worksheet
          'eLROW + 1 is the next blank row in column E
          .Cells(eLROW + 1, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=True
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top