VBA: Copy and paste next available column to a specific sheet pending on value of cell.

Gmonzie

New Member
Joined
Apr 7, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hey guys,

New to VBA so struggling here.

1617819652960.png


I want to copy and paste data to a specific sheet (paste onto the next available column) depending on the value of a cell. Ex. If J6 says "Broadway", I want to copy and paste J6:J33 to sheet named "Broadway" to the next available column. Any help would be much appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
into row 6 again?

VBA Code:
Sub copytonext()

Dim lastcol As Long
Dim ws As Worksheet
Dim wsdname As String
Dim wsdest As Worksheet

Set ws = ActiveSheet
wsdname = ws.Range("J6")
Set wsdest = Sheets(wsdname)
lastcol = wsdest.Cells(6, wsdest.Columns.Count).End(xlToLeft).Column

ws.Range("J6:J33").Copy wsdest.Cells(6, lastcol + 1)


End Sub
 

Gmonzie

New Member
Joined
Apr 7, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
into row 6 again?

VBA Code:
Sub copytonext()

Dim lastcol As Long
Dim ws As Worksheet
Dim wsdname As String
Dim wsdest As Worksheet

Set ws = ActiveSheet
wsdname = ws.Range("J6")
Set wsdest = Sheets(wsdname)
lastcol = wsdest.Cells(6, wsdest.Columns.Count).End(xlToLeft).Column

ws.Range("J6:J33").Copy wsdest.Cells(6, lastcol + 1)


End Sub
Hi! Thank you for your help! What do you mean by "into row 6 again?" I have multiple sheets. The screenshot below is my first sheet (cover sheet if you will) named Data Entry:
1617890053754.png

Following, I have 10 sheets that is respective to the drop down list in J6. ex, list shows: Broadway, Main St, Chambers, etc and have sheets named Broadway, Main St, Chambers, etc. If J6 has the word "Main St" then I want the data in J6:J33 to be pasted (values only) into sheet called "Main St" to the next available column.

I tried to use this code but had a bug error. Thank you!
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
622
Office Version
  1. 2010
Platform
  1. Windows
Hi ! As a beginner starter :​
VBA Code:
Sub Demo1()
    If Evaluate("(ISREF('" & [J6].Text & "'!A1)") Then
        With Sheets([J6].Text).UsedRange
            .Cells(1, .Columns.Count + 1).Resize(28).Value = [J6:J33].Value
        End With
    End If
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
622
Office Version
  1. 2010
Platform
  1. Windows
There is a typo in the Evaluate formula, it must be without the red char "(ISREF('" so "ISREF('" …​
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,791
What was the error? Into row 6 again as in pasting into. You didn't specify the row, just the next column.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,214
Messages
5,640,910
Members
417,178
Latest member
HelpMeExcelExperts

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
Top